Selecitng Data

Basic SELECT Statements

SELECT *
FROM customers
WHERE customer_id = 1
ORDER BY first_name;

Selecting Specific Columns

SELECT 
    first_name, 
    last_name, 
    points, 
    points + 10, 
    points / 10 + 100,
    (points + 10) * 100 AS discount_factor,
    (points + 10) * 100 AS 'discount factor'
FROM customers;

Note: Use quotes when column names contain spaces.

SELECT DISTINCT state
FROM customers;

Filtering with WHERE

SELECT *
FROM customers
WHERE points > 3000;

SELECT *
FROM customers
WHERE state = 'VA';

SELECT *
FROM customers
WHERE state <> 'VA';

SELECT *
FROM customers
WHERE birth_date > '1990-01-01';

Logical Operators: AND, OR, NOT

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' 
    OR (points > 1000 AND state = 'VA');

Operator precedence: AND takes priority over OR.

NOT (birth_date > '1990-01-01' OR points > 1000);
birth_date <= '1990-01-01' AND points <= 1000;

IN Operator

SELECT *
FROM customers
WHERE state IN ('VA', 'FL', 'GA');

SELECT *
FROM customers
WHERE state NOT IN ('VA', 'FL', 'GA');

BETWEEN Operator

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000;

Pattern Matching with LIKE

WHERE last_name LIKE 'b%';    -- starts with b
WHERE last_name LIKE '%b%';  -- contains b
WHERE last_name LIKE '%y';   -- ends with y
WHERE last_name LIKE '___y'; -- four chars ending with y
WHERE last_name LIKE 'b__y'; -- starts with b, ends with y, 2 chars between

Wildcards: % matches any number of characters, _ matches exactly one character.

SELECT *
FROM customers
WHERE address LIKE '%trail%' 
    OR address NOT LIKE '%avenue%';

REGEXP for Advanced Pattern Matching

WHERE last_name REGEXP 'field';      -- contains field anywhere
WHERE last_name REGEXP '^field';     -- starts with field
WHERE last_name REGEXP 'field$';     -- ends with field
WHERE last_name REGEXP 'field|mac';  -- contains either
WHERE last_name REGEXP 'field|mac|rose';
WHERE last_name REGEXP '^field|mac|rose'; -- starts with field OR contains mac OR contains rose
WHERE last_name REGEXP '[gim]e';     -- ge, ie, or me
WHERE last_name REGEXP '[a-h]e';     -- ae through he

Handling NULL Values

WHERE phone IS NULL;
WHERE phone IS NOT NULL;

Sorting Results with ORDER BY

SELECT *
FROM customers
ORDER BY first_name;           -- ascending (default)
ORDER BY first_name DESC;      -- descending
ORDER BY state, first_name;    -- multiple columns
ORDER BY state DESC, first_name;

SELECT birth_date, first_name, last_name, 10 AS points 
FROM customers
ORDER BY 1, 2;  -- refer to column positions

SELECT *, quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC;

Limiting Results

LIMIT 3;      -- return first 3 rows
LIMIT 6, 3;   -- skip 6, return rows 7-9

The LIMIT clause must appear last in the query.

Combining Tables with JOINs

Inner JOIN

SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

When column names appear in multiple tables, qualify them:

SELECT orders.customer_id, first_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

Table aliases streamline queries:

SELECT order_id, o.customer_id, first_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Cross-Database JOINs

USE sql_store;

SELECT *
FROM order_items oi
JOIN sql_inventory.products p ON oi.product_id = p.product_id;

Self JOIN

SELECT *
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id;

Multiple Table JOINs

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON o.status = os.order_status_id;

Compoiste JOIN Conditions

SELECT *
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id;

Implicit JOIN Syntax

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

OUTER JOINs

Inner joins return only matching rows. Outer joins return all rows from one or both tables.

SELECT *
FROM customers c
LEFT JOIN orders o ON c.customer_id = c.customer_id
ORDER BY c.customer_id;

LEFT JOIN returns all customers regardless of order history. RIGHT JOIN returns all orders.

Multiple Table OUTER JOINs

SELECT *
FROM customers c
LEFT JOIN orders o ON c.customer_id = c.customer_id
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id;

Self OUTER JOIN

SELECT *
FROM employees e
LEFT JOIN employees m ON e.reports_to = m.employee_id;

This returns managers in the results even when they have no direct reports.

USING Clause

When join columns share the same name:

-- Instead of:
ON o.customer_id = c.customer_id

-- Use:
USING(customer_id);

Multiple columns:

USING(order_id, product_id);

Natural JOIN

SELECT *
FROM orders o
NATURAL JOIN customers c;

The database engine infers the join condition automatically—use with caution.

CROSS JOIN

Produces a Cartesian product of both tables:

SELECT *
FROM orders o
CROSS JOIN products p;

Generates every possible combination of rows from both tables.