Quartz 5

SQL Keywords

Properties1
tagsdatabase/postgres, learningplatform/coursera, scriptinglanguage/sql

3 min read

SQL Keywords

Joins

The JOIN operation links across several tables as part of a SELECT operation, we must tell JOIN how to use the KEYS that make up the connections using an ON clause. See SQL Joins for all join types (INNER, LEFT, RIGHT, FULL, CROSS, SELF).

Here is a simple example

SELECT album.title, artist.name
FROM album
JOIN artist ON album.artist_id = artist.id;

Alter

The alter command can be used to make changes to the database schema after it has been created, such as changing the length of a datatype from SMALLINT to BIGINT or changing the length of the VARCHAR(x) to VARCHAR(y), it is a very useful command that helps us in maintenance, such as adding or removing columns.

Some examples:

ALTER TABLE fav DROP COLUMN test;
ALTER TABLE game ALTER COLUMN description TYPE TEXT;

Distinct

Using the DISTINCT keyword we can reduce the total result set by retrieving only the unique rows.

Using DISTINCT ON we can limit the duplicate removal to a specific column.

SELECT DISTINCT model FROM racing;
SELECT DISTINCT ON (model) make, model FROM racing;

Group By

Using the GROUP BY we can reduce the total rows by using aggregate functions to group certain rows.

Such as when we have duplicate values for a column, we can group those columns and count those values.

SELECT COUNT(abbrev), abbrev FROM pg_timezone_names GROUP BY abbrev;

GROUP BY always comes after the WHERE clause/keyword.

HAVING

HAVING filters groups after aggregation (where WHERE filters rows before aggregation):

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

ORDER BY

Sorts results by one or more columns. ASC (default) or DESC.

SELECT name, salary FROM employees ORDER BY salary DESC, name ASC;

NULLS FIRST / NULLS LAST controls null placement.

LIMIT and OFFSET

Restrict the number of rows returned. Essential for pagination.

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;  -- Page 3 of 10

UNION / INTERSECT / EXCEPT

Set operations combining results of two queries. Both queries must have the same number of columns with compatible types.

-- All unique cities from both tables
SELECT city FROM customers UNION SELECT city FROM suppliers;
 
-- Cities in both tables
SELECT city FROM customers INTERSECT SELECT city FROM suppliers;
 
-- Cities in customers but not suppliers
SELECT city FROM customers EXCEPT SELECT city FROM suppliers;

Use UNION ALL to keep duplicates (faster — no dedup step).

EXISTS

Tests whether a subquery returns any rows. Efficient for checking existence:

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

CASE

Inline conditional logic (see also Functions):

SELECT name,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_label
FROM users;

COALESCE

Returns the first non-null value. Useful for defaults:

SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name FROM users;

CAST / ::

Type conversion between SQL Data Types:

SELECT CAST('2026-01-15' AS DATE);
SELECT '42'::INTEGER;

SQL Execution Order

Understanding the order SQL statements are processed:

graph TD
    A[FROM / JOIN] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E[SELECT]
    E --> F[DISTINCT]
    F --> G[ORDER BY]
    G --> H[LIMIT / OFFSET]

This is why you cannot reference a column alias from SELECT in WHERE — WHERE runs before SELECT.

Graph View