SQL basic

1. SELECT, WHERE, ORDER BY

• Syntax: SELECT column FROM table WHERE condition ORDER BY column [ASC|DESC].
• Common mistake: incorrect keyword order, such as writing ORDER DESC BY total instead of the correct ORDER BY total DESC (ASC/DESC goes after the column name, not between ORDER and BY).

2. JOIN

• JOIN without specifying a type defaults to INNER JOIN: it only returns rows when there is a match in both tables. Any row in either table that does not find a corresponding match is excluded from the result.

SELECT customers.name AS customer_name, orders.total
FROM customers
JOIN orders ON customers.id = orders.customer_id;

• LEFT JOIN: retains all rows from the left table (the table placed before LEFT JOIN), regardless of whether a match is found in the right table. If no match exists, the columns retrieved from the right table evaluate to NULL.

SELECT customers.name AS customer_name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

• Use case: a report to "list all customers including those who have never placed an order" requires LEFT JOIN; using INNER JOIN would drop customers without orders.

3. GROUP BY, COUNT

SELECT customers.name AS customer_name, COUNT(orders.id) AS number_order
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

• Most SQL engines require every non-aggregated column (outside functions like COUNT()) to appear in the GROUP BY clause.
• COUNT() counts non-NULL values in the specified column. Therefore, COUNT(primary_key_column) (such as orders.id) is safer than COUNT(other_column), because primary keys are guaranteed non-null when a row actually exists, avoiding undercounting when other columns contain missing NULL values (unrelated to the LEFT JOIN).

4. UPDATE

UPDATE orders
SET status = 'shipped'
WHERE orders.id = 10;

• The WHERE clause requires extreme caution in UPDATE (as well as DELETE) queries: omitting WHERE applies the modification across the entire table, making it an extremely common and dangerous operational mistake in production.