TwitterFacebook
The Ultimate SQL SELECT Cheat Sheet

The Ultimate SQL SELECT Cheat Sheet

Have you ever found yourself staring blankly at your SQL code, trying to recall the syntax for a SELECT query? Don't worry; you're not alone. SQL, with its vast array of commands and clauses, can be daunting to remember every detail. Here I will share a useful cheat sheet to write the most common SELECT queries.

The Power of a Cheat Sheet

Memorizing every SQL command and its syntax is not easy, that's where cheat sheets come to the rescue. Instead of flipping through documentation a cheat sheet provides quick and easy access to essential commands.

Deciphering the SELECT Query

Let's check the anatomy of a SELECT query. At its core, this query retrieves data from one or more tables in a database. Here's a breakdown of its components:

  • SELECT: This keyword specifies the columns you want to retrieve from the database. You can either select specific columns or use the wildcard (*) to fetch all columns.
  • FROM: Here, you specify the table or tables from which you want to retrieve data.
  • DISTINCT (optional): This optional keyword removes duplicate rows from the result set.
  • WHERE(optional): This optional clause filters rows based on specified conditions. It allows you to narrow down your results to only those that meet certain criteria.
  • GROUP BY(optional): When you want to group your results based on the values of one or more columns, you use this clause. It's commonly used in conjunction with aggregate functions.
  • HAVING(optional): Similar to the WHERE clause, HAVING filters grouped rows based on specified conditions. It comes into play after the GROUP BY clause.
  • ORDER BY (optional): This optional clause sorts the result set based on specified columns or expressions.

The base for a SELECT query will look like this:

-- [Mandatory] SELECT: This keyword specifies the columns you want to retrieve from the database.
SELECT 
    column1, -- Specify columns to retrieve or * to fetch all columns
    column2, 
    ...
    -- [Optional] DISTINCT: This keyword removes duplicate rows from the result set, ensuring only unique rows are returned.
    -- Tipically used immediately after the SELECT keyword.
    -- For example:
    -- SELECT DISTINCT column1, column2 FROM table_name;
-- [Mandatory] FROM: Here, you specify the table or tables from which you want to retrieve data.
FROM 
    table1, -- [Mandatory] Specify at least one table
    table2, 
    ...;
    
-- [Optional] WHERE: This clause filters rows based on specified conditions.
-- For example:
-- SELECT * FROM employees WHERE salary > 50000;
    
-- [Optional] GROUP BY: When you want to group your results based on the values of one or more columns,
-- you use this clause. It's commonly used in conjunction with aggregate functions.
-- For example:
-- SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

-- [Optional] HAVING: Similar to the WHERE clause, HAVING filters grouped rows based on specified conditions.
-- It comes into play after the GROUP BY clause.
-- For example:
-- SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 3;

-- [Optional] ORDER BY: This clause sorts the result set based on specified columns or expressions.
-- It's typically used to arrange the data in ascending or descending order.
-- For example:
-- SELECT employee_name, salary FROM employees ORDER BY salary DESC;

Some examples

This query retrieves all columns from the employees table without applying any filtering criteria.

-- Basic Example without WHERE Clause
SELECT *
FROM employees;

This query uses the DISTINCT keyword to retrieve unique values of the department_id column from the employees table.

-- Example with DISTINCT
-- This query retrieves distinct values of the department_id column from the employees table.
SELECT DISTINCT department_id
FROM employees;

This query filters employee records based on the salary column, only returning those with a salary greater than $50,000.

-- Example with WHERE Clause
-- This query retrieves employee records with a salary greater than $50,000.
SELECT employee_name, salary
FROM employees
WHERE salary > 50000;

This query groups employee records by department and calculates the count of employees in each department.

-- Example with GROUP BY Clause
-- This query counts the number of employees in each department.
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

This query first groups employee records by department and then filters the grouped results to only include departments with more than three employees.

-- Example with HAVING Clause
-- This query finds departments with more than three employees.
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3;

This example retrieves employee records and sorts them by salary in descending order using the ORDER BY clause.

-- Example with ORDER BY Clause
-- This query retrieves employee records sorted by salary in descending order.
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

Happy querying!