SQL queries: examples explained

The two tables everything is shown on#

All the examples use a pair of related tables. The first holds employees, the second departments; they are linked by the numeric column dept_id.

employees
 id | name  | dept_id | salary
----+-------+---------+--------
  1 | Alice |       1 |  70000
  2 | Bob   |       1 |  90000
  3 | Carol |       2 | 120000
  4 | Dave  |       2 | 150000
  5 | Eve   |  (NULL) |  60000

departments
 id | title
----+-------------
  1 | Sales
  2 | Engineering
  3 | Logistics

Note two details: Eve has no department filled in, and the Logistics department has nobody in it yet. These two are what will show the difference between kinds of join.

The syntax in the examples is standard and works the same in PostgreSQL, SQLite and MySQL. Differences start further on — in data types, date functions and the way you limit the output.

SELECT and WHERE: filtering rows#

SELECT lists the columns you need, FROM names the table, and WHERE filters rows by a condition.

SELECT name, salary
FROM employees
WHERE salary >= 90000;

 name  | salary
-------+--------
 Bob   |  90000
 Carol | 120000
 Dave  | 150000

Conditions combine with AND, OR and parentheses. Three separate forms are useful: BETWEEN for a range, IN for a list of values, and LIKE for a string pattern, where % stands for any number of characters.

SELECT name FROM employees WHERE salary BETWEEN 70000 AND 120000;
SELECT name FROM employees WHERE dept_id IN (1, 2);
SELECT name FROM employees WHERE name LIKE 'C%';

You cannot compare an empty value with an equals sign: dept_id = NULL returns nothing, not even for Eve. Emptiness has its own check:

SELECT name FROM employees WHERE dept_id IS NULL;

 name
------
 Eve

ORDER BY and LIMIT: order and cut-off#

Row order is not guaranteed unless you specify it — ORDER BY does that, and ASC and DESC choose the direction.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2;

 name  | salary
-------+--------
 Dave  | 150000
 Carol | 120000

You can sort by several columns at once: ORDER BY dept_id, salary DESC first groups by department and then puts the best-paid people first within each one. PostgreSQL, SQLite and MySQL support LIMIT; SQL Server uses TOP instead, and older Oracle versions use a condition on ROWNUM.

GROUP BY and HAVING: counting by group#

Aggregate functions collapse many rows into one number: COUNT, SUM, AVG, MIN, MAX. GROUP BY says which attribute to cut the table into groups by.

SELECT dept_id, COUNT(*) AS people, SUM(salary) AS fund
FROM employees
GROUP BY dept_id;

 dept_id | people |  fund
---------+--------+--------
       1 |      2 | 160000
       2 |      2 | 270000
  (NULL) |      1 |  60000

The row order here is the one PostgreSQL returns. Without ORDER BY the order is not guaranteed at all: SQLite, for example, puts the row with the empty department first. If you need a specific order, write ORDER BY explicitly.

COUNT(*) counts rows, while COUNT(dept_id) counts only non-empty values of the column. On this table the first gives 5 and the second 4: Eve's row does not count. Interviewers ask about this difference more often than about any other SQL detail.

WHERE filters rows before grouping, HAVING filters finished groups after it.

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 100000;

 dept_id |      avg_salary
---------+----------------------
       2 | 135000.0000000000000

The average is a fractional number even where the division comes out even: the result type of AVG is wider than an integer. You round it on output — for example, ROUND(AVG(salary)).

Department 1, with an average of 80,000, did not make it into the result, and neither did Eve's row. The order of the parts of a query is fixed: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. The database will not let you rearrange them.

The four kinds of JOIN#

A join glues rows of two tables together by a condition. The kinds differ in what they do with rows that found no partner.

INNER JOIN keeps only the matched pairs.

SELECT e.name, d.title
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

 name  |    title
-------+-------------
 Alice | Sales
 Bob   | Sales
 Carol | Engineering
 Dave  | Engineering

Four rows: Eve dropped out (her department is empty), and Logistics dropped out (nobody works there).

LEFT JOIN keeps every row of the left table, filling in NULL where there is no partner.

SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

 name  |    title
-------+-------------
 Alice | Sales
 Bob   | Sales
 Carol | Engineering
 Dave  | Engineering
 Eve   | (NULL)

Five rows — every employee is there. This is the most common kind of join: "show me everyone, plus whatever is known about them".

RIGHT JOIN does the mirror image: it keeps every row of the right table.

SELECT e.name, d.title
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

  name  |    title
--------+-------------
 Alice  | Sales
 Bob    | Sales
 Carol  | Engineering
 Dave   | Engineering
 (NULL) | Logistics

Five rows again, but this time the extra one is not Eve but the empty Logistics department. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables, so in practice people rarely write it — especially since older SQLite builds do not support it at all.

FULL OUTER JOIN keeps both sides at once: the four matched pairs, plus Eve without a department, plus Logistics without people — six rows. MySQL does not have this kind; you build it from a LEFT JOIN and a RIGHT JOIN combined with UNION.

If you forget the ON condition, you get a Cartesian product: every row with every row, 5 × 3 = 15 rows. On tables with millions of records such a query brings the database to a halt, so check the join condition first.

Subqueries#

A subquery is an ordinary SELECT inside another query. A scalar subquery returns a single value and can be used in a comparison:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The table average is 98,000, so the result is:

 name  | salary
-------+--------
 Carol | 120000
 Dave  | 150000

A subquery that returns a list goes into IN:

SELECT name
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE title = 'Engineering');

 name
-------
 Carol
 Dave

To check whether a partner exists, use EXISTS — the database stops at the first match and does not build the whole list:

SELECT d.title
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

It returns one row — Logistics. But NOT IN with a subquery that may contain a NULL returns an empty result, and that is not a database bug but the rules of three-valued logic. The habit of writing negation with NOT EXISTS saves you from a whole class of traps.

Common mistakes#

A column in SELECT that is not in GROUP BY. If you group by dept_id, you cannot also show name: a group of two people does not have a single name. PostgreSQL refuses outright; MySQL in non-strict mode silently returns an arbitrary value.

Comparing with NULL. salary != 100000 does not return rows where the salary is empty. You need to add OR salary IS NULL.

UPDATE and DELETE without WHERE. They run against the whole table. The rule: first run the same filter as a SELECT, look at the rows, and only then change the keyword.

Testing queries on a production database. Set up a separate practice copy — for example, a local PostgreSQL or a single SQLite file. Nobody minds if you break its tables. A full study plan is in how to learn SQL from scratch.

When a query returns the right answer but slowly, the next step is putting EXPLAIN in front of it: the database shows whether it reads the whole table or uses an index.

Step-by-step plan

1. Set up a practice databaseCreate the two tables from the example and fill them with five and three rows.
2. Filtering and sortingWrite five queries with WHERE, BETWEEN, IN, LIKE and ORDER BY, and check the results by hand.
3. GroupingCompute COUNT, SUM and AVG by department, then cut groups off with HAVING.
4. All four joinsRun INNER, LEFT, RIGHT and FULL on the same tables and write down how many rows each returned and why.
5. SubqueriesFind the employees above the average salary and the departments without people using NOT EXISTS.

Check yourself

Sources

• PostgreSQL documentationThe primary source on SELECT, JOIN and aggregate syntax
  free
• SQLite documentationA database in a single file — easy to practise on without installing a server
  free
• SQLZooFree interactive SQL exercises with answers checked in the browser
  free

Was this helpful?

Yes No