AI Hints
Homeโ€บSQL Tutorialโ€บJoining Tables
SQL ยท Lesson 44 of 126

Short Names for Tables

Table aliases.

โฑ 2 min readโ–ถ Video at 1:01:47๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Typing orders dot, and customers dot, again and again gets tiring. And in bigger queries, it makes the code long and hard to read. So everyone uses short nicknames for tables.

Short Names for Tables
From the video: Short Names for Tables
  • orders. customers. orders. customers.
  • Long and tiring
  • Use short nicknames

Table aliases

After the table name, write as, and a short name. Orders as o, and customers as c. Now o dot order ID means the order ID column from orders. This is the same query as before, just much easier to read. Run it. Same idea, and now we've also added the country.

query.sqlsql
SELECT o.order_id,
       o.order_date,
       c.first_name,
       c.country
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
LIMIT 4;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ order_id โ”‚ order_date โ”‚ first_name โ”‚  country  โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1001     โ”‚ 2025-03-06 โ”‚ Tara       โ”‚ Canada    โ”‚
โ”‚ 1002     โ”‚ 2024-11-24 โ”‚ Tara       โ”‚ Canada    โ”‚
โ”‚ 1003     โ”‚ 2024-06-22 โ”‚ Grace      โ”‚ Australia โ”‚
โ”‚ 1004     โ”‚ 2025-02-19 โ”‚ Grace      โ”‚ Australia โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

JOIN with WHERE

Joins work with everything you already know. Here we find all the orders placed by one customer, Tara Brown. The join brings in the names, and where filters on them. Run it. Tara has placed two orders, and both were delivered.

query.sqlsql
SELECT o.order_id, o.order_date, o.status
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.first_name = 'Tara'
  AND c.last_name = 'Brown';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ order_id โ”‚ order_date โ”‚  status   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1001     โ”‚ 2025-03-06 โ”‚ delivered โ”‚
โ”‚ 1002     โ”‚ 2024-11-24 โ”‚ delivered โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Cancelled in Dublin

Your turn. Find the cancelled orders from customers who live in Dublin. Show the order ID, the status, and the city. Pause and try. Here's one way. Join orders to customers, then filter on the city and the status.

โœ๏ธ Exercise: Cancelled in Dublin

Cancelled orders from customers in Dublin: order_id, status, city

Show hint

JOIN, then WHERE city = 'Dublin' AND status = 'cancelled'

Show solution
solution.sqlsql
SELECT o.order_id, o.status, c.city
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.city = 'Dublin'
  AND o.status = 'cancelled';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ order_id โ”‚  status   โ”‚  city  โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1494     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 1757     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 1934     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 1941     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 1976     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 1985     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 1996     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 2263     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 2402     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 2470     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 2555     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 2560     โ”‚ cancelled โ”‚ Dublin โ”‚
โ”‚ 2561     โ”‚ cancelled โ”‚ Dublin โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Short table names keep joins tidy, and joins work with where, group by, and everything else. Next, what happens to rows that have no match.

Key takeaways
  • FROM orders AS o
  • o.order_id
  • JOIN + WHERE
Next up: Inner Joins Drop Rows โ€” what happens with no match