ID: I202601271121 Status: idea Tags: SQL

SQL queries cheatsheet

Most SQL queries that you’ll use will be quite simple, but that doesn’t take away the fact that you can do a lot of complex things with sql queries.

Structure

In general there is a specific order to do things with sql.

SELECT <the_fields_you_want_returned> FROM <table_name> WHERE <filtering> AND <more_optional_filtering> ORDER BY <column_name> asc

JOIN

There are a lot of different ways to join tables together. A generic join would look something along the lines of: SELECT fa.name FROM film f JOIN film_actor fa ON fa.film_id = f.id WHERE f.id = 100. This will return all actor names that worked on the film with ID 100. But if we were to not filter with WHERE, we would get all actors, even the ones that aren’t assigned to any film. And that is where LEFT and RIGHT JOIN comes in.

As the above image shows, you can select all documents that overlap, all documents that don’t overlap. etc etc. The joins are by default INCLUSIVE.

The structure of a JOIN is always the same:

SELECT __ FROM __ JOIN __ ON __ = __ WHERE __

Or if you are feeling fancy and using AS:

SELECT __ FROM __ AS __ JOIN __ AS __ ON __ = __ WHERE __

Here are concise, practical JOIN examples using the Sakila sample database.

• INNER JOIN: only overlapping rows

-- All actors for a specific film (film_id = 100)
SELECT a.actor_id, a.first_name, a.last_name
FROM film AS f
JOIN film_actor AS fa ON fa.film_id = f.film_id
JOIN actor AS a ON a.actor_id = fa.actor_id
WHERE f.film_id = 100;

-- Films with their category name
SELECT f.film_id, f.title, c.name AS category
FROM film AS f
JOIN film_category AS fc ON fc.film_id = f.film_id
JOIN category AS c ON c.category_id = fc.category_id;

• LEFT JOIN: keep all rows from left side, even if no match on the right

-- All films and (if present) their category; films without a category appear with NULL category
SELECT f.film_id, f.title, c.name AS category
FROM film AS f
LEFT JOIN film_category AS fc ON fc.film_id = f.film_id
LEFT JOIN category AS c ON c.category_id = fc.category_id
ORDER BY f.film_id, c.name;

-- All actors and how many films they have acted in (zero if none)
SELECT a.actor_id,
       a.first_name,
       a.last_name,
       COUNT(fa.film_id) AS film_count
FROM actor AS a
LEFT JOIN film_actor AS fa ON fa.actor_id = a.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY film_count DESC, a.actor_id;

• RIGHT JOIN: keep all rows from right side, even if no match on the left Note: MySQL supports RIGHT JOIN, but many developers prefer rewriting as LEFT JOIN by swapping table order.

-- All categories and their films (films missing appear as NULL when category has none)
SELECT c.category_id, c.name AS category, f.film_id, f.title
FROM film AS f
RIGHT JOIN film_category AS fc ON fc.film_id = f.film_id
RIGHT JOIN category AS c ON c.category_id = fc.category_id
ORDER BY c.category_id, f.title;

Equivalent using LEFT JOIN (often clearer/portable):

SELECT c.category_id, c.name AS category, f.film_id, f.title
FROM category AS c
LEFT JOIN film_category AS fc ON fc.category_id = c.category_id
LEFT JOIN film AS f ON f.film_id = fc.film_id
ORDER BY c.category_id, f.title;

• FULL OUTER JOIN pattern: show matches and non-matches from both sides MySQL lacks FULL OUTER JOIN; emulate via UNION of LEFT and RIGHT with anti-matches filtered.

-- All films with categories where present, plus films with no category and categories with no films
SELECT f.film_id, f.title, c.category_id, c.name AS category
FROM film AS f
LEFT JOIN film_category AS fc ON fc.film_id = f.film_id
LEFT JOIN category AS c ON c.category_id = fc.category_id
UNION
SELECT f.film_id, f.title, c.category_id, c.name AS category
FROM category AS c
LEFT JOIN film_category AS fc ON fc.category_id = c.category_id
LEFT JOIN film AS f ON f.film_id = fc.film_id
WHERE f.film_id IS NULL;

• Anti-join: rows in left table with no match in right (NOT EXISTS pattern)

-- Films that have never been rented (no row in rental via inventory)
SELECT f.film_id, f.title
FROM film AS f
WHERE NOT EXISTS (
  SELECT 1
  FROM inventory AS i
  WHERE i.film_id = f.film_id
    AND EXISTS (
      SELECT 1
      FROM rental AS r
      WHERE r.inventory_id = i.inventory_id
    )
);

-- Customers who have never rented anything
SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customer AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM rental AS r
  WHERE r.customer_id = c.customer_id
);

• Semi-join: rows in left table that have at least one match in right (EXISTS)

-- Films that have at least one copy in store 1
SELECT f.film_id, f.title
FROM film AS f
WHERE EXISTS (
  SELECT 1
  FROM inventory AS i
  WHERE i.film_id = f.film_id
    AND i.store_id = 1
);

• Multi-join with filters: joining across several relationships

-- Top 10 most-rented films (title plus rental count)
SELECT f.film_id, f.title, COUNT(*) AS rentals
FROM rental AS r
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film AS f ON f.film_id = i.film_id
GROUP BY f.film_id, f.title
ORDER BY rentals DESC, f.film_id
LIMIT 10;

-- Staff member, their store, and customers they serve (including staff with no rentals)
SELECT s.staff_id,
       s.first_name,
       s.last_name,
       st.store_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       r.rental_id
FROM staff AS s
JOIN store AS st ON st.store_id = s.store_id
LEFT JOIN rental AS r ON r.staff_id = s.staff_id
LEFT JOIN customer AS c ON c.customer_id = r.customer_id
ORDER BY s.staff_id, r.rental_id;

• Self-join: not common in Sakila but illustrative (e.g., films with same length)

-- Pairs of different films that have the exact same length
SELECT f1.film_id AS film_id_1,
       f1.title AS title_1,
       f2.film_id AS film_id_2,
       f2.title AS title_2,
       f1.length
FROM film AS f1
JOIN film AS f2
  ON f2.length = f1.length
 AND f2.film_id > f1.film_id;

• Join with ON vs WHERE nuance: moving conditions between ON and WHERE

-- LEFT JOIN keeping films without inventory by placing store filter in ON
SELECT f.film_id, f.title, i.inventory_id
FROM film AS f
LEFT JOIN inventory AS i
  ON i.film_id = f.film_id
 AND i.store_id = 1;          -- filter in ON keeps unmatched films as NULL
 
-- If you move "i.store_id = 1" to WHERE, it would turn this into an INNER JOIN.

Functions

There are a few functions in MySQL, you can use these for more complex things.

COUNT

-- counts all films by rental rate
SELECT rental_rate, COUNT(*) AS film_count FROM film GROUP BY rental_rate;
 
-- counts all unique / distinct languages
SELECT COUNT(DISTINCT language_id) AS language_count FROM film;

AVG

-- returns the average rental rate
SELECT AVG(rental_rate) AS avg_rental_rate FROM film;

---

References

I was preparing for the Avans 2.1 Remindo Test