Querying Data with SQL

Almost every app keeps its data in a database, and most databases are queried with SQL. You describe the rows you want and the database works out how to find them. A handful of keywords covers most of what a beginner project needs, and a few traps cause most of the bugs. All examples below use these two tables: users (id, name, email, country, phone) and orders (id, user_id, total, created_at).

Tables, rows and SELECT

A table is like a spreadsheet with strict rules: each column has a name and a type, and each row is one record. SELECT picks the columns, FROM picks the table, and WHERE keeps only the rows that match:

SELECT name, email
FROM users
WHERE country = 'VN';

Combine conditions with AND and OR, and compare with =, <> (not equal), <, >. Text values go in single quotes.

Sorting and limiting

A table has no built-in order. Without ORDER BY the database returns rows in whatever order is convenient, and that order can change as data changes. So "the five latest orders" needs both:

SELECT id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 5;

DESC sorts newest first. A LIMIT without ORDER BY gives you five rows, but not any particular five.

Joining two tables

Orders store user_id, not the user's name. To show both, join the tables on the column that links them:

SELECT users.name, orders.total
FROM orders
JOIN users ON users.id = orders.user_id;

A join produces one row for each matching pair. A user with three orders appears three times, which is right for listing orders but surprising if you only wanted a list of names (use SELECT DISTINCT users.name for that). Always check the ON condition: ON users.id = orders.id runs without error but matches order numbers to user numbers, which is nonsense.

A plain JOIN only keeps rows that have a match on both sides. LEFT JOIN keeps every row from the left table, filling the right side with NULL when there is no match.

Counting with GROUP BY

Aggregate functions such as COUNT, SUM and AVG squash many rows into one number. GROUP BY gives you one number per group:

SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.name;

The LEFT JOIN keeps users with no orders, and COUNT(orders.id) counts only real orders, giving them 0. COUNT(*) counts rows, and a user with no orders still has one row (full of NULLs), so it would wrongly report 1.

NULL means unknown

NULL is not zero or an empty string; it means "no value". Comparisons with NULL are never true, not even NULL = NULL, so WHERE phone = NULL returns nothing. Use WHERE phone IS NULL or IS NOT NULL.

Aggregates skip NULLs too: COUNT(phone) counts only rows that have a phone, and AVG(rating) divides by the number of non-NULL ratings, not the number of rows.

Never build SQL from strings

This code is a security hole:

// Never do this
const sql = "SELECT * FROM users WHERE email = '" + email + "'";

If someone types ' OR '1'='1 as their email, the query becomes WHERE email = '' OR '1'='1', which matches every user. That is SQL injection, and it can read or delete entire tables. Use a parameterized query instead, where the SQL and the values travel separately:

const result = await db.query(
  'SELECT * FROM users WHERE email = $1',
  [email],
);

The database treats $1 as a value only, never as SQL, whatever characters it contains. Escaping quotes by hand is not a substitute; it is easy to miss a case.

Try it: in any SQL playground, create the two tables, insert a user with no orders, and compare the counts you get from JOIN with COUNT(*) against LEFT JOIN with COUNT(orders.id).