DQL — Data Query Language

1. SELECT Statement

SELECT is the basis of everything. The order of the clauses is always the same:

SELECT  ….….  AS …, …      -- which columns? (table.column)
                            -- SUM(...), COUNT(*) with GROUP BY
FROM    …                   -- first table (order doesn't matter)
JOIN    … ON ….…. = ….….   -- if PK and FK don't share a name: table.column
…                           -- join as many tables as you like
WHERE   …                   -- filtering: =, <>, LIKE, AND, OR, ...
GROUP BY …,…                -- group records based on equal column values
HAVING  …                   -- filter after grouping
ORDER BY …,…                -- sort by columns

Table name

The table name is required whenever there's potential naming ambiguity. Use tablename.columnname or an alias for the table name.

Example

SELECT  customers.customerName AS customer,
        orderDate,
        SUM(quantityOrdered * priceEach) AS amount
FROM    orderdetails
    JOIN orders
        ON orders.orderNumber = orderdetails.OrderNumber
    JOIN customers
        ON customers.customerNumber = orders.customerNumber
WHERE   orders.status = 'Shipped'
    AND shippedDate > '2004-01-01'
GROUP BY orders.orderNumber, orderDate, customers.customerName
HAVING  SUM(quantityOrdered * priceEach) < 10000
ORDER BY amount DESC;

---

2. JOIN Types

INNER JOIN — 1-N Relationship

Most common. Returns only rows that have a match in both tables.

• 1-N: A has N B's (0, 1 or more) — B belongs to exactly 1 A
• Example: a customer has 0 or more orders, each order belongs to 1 customer

SELECT *
FROM a
    JOIN b ON a.aID = b.aID

INNER JOIN — a Chain of 1:N Relationships

SELECT *
FROM a
    JOIN b ON a.aID = b.aID
    JOIN c ON c.bID = b.bID

INNER JOIN — M-N Relationship

A many-to-many relationship requires a mapping table with foreign keys to both tables.

-- person_book is the mapping table
SELECT *
FROM person_book
    JOIN person ON person.id = person_book.person_id
    JOIN book    ON book_id    = person_book.book_id

---

3. Other JOIN Types

Type	Result
INNER JOIN	Only rows with a match in both tables
LEFT JOIN	All rows from the left table + matches from the right (NULL if no match)
OUTER JOIN	All rows from both tables (including unmatched ones)
CROSS JOIN	Cartesian product — every row of A paired with every row of B
UNION	Combines the results of two queries (removes duplicates)