CodeBlanks Logo Icon CodeBlanks
Login Register

Table aliases

Give a table a short name in FROM and JOIN.

Multi-table JOIN

You can join many tables with successive JOINs. Each JOIN gets its own ON condition.

SELECT o.id, c.name, p.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

This query combines order, customer and product in one query.

o.id, c.name, p.name is selected from orders o. customers c is joined on o.customer_id = c.id, keeping only matching rows. products p is joined on o.product_id = p.id, keeping only matching rows.

Multi-table JOIN — details

Each JOIN merges the previous result with a new table. In multi-table queries aliases are almost mandatory.

Practice Multi-table JOIN

Join orders, customers and products on customer_id and product_id.

Starter code

query.sql
-- join three tables
SELECT o.id, c.name, p.name
FROM orders o
JOIN

Sample solution

query.sql
SELECT o.id, c.name, p.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id

Expected output

id  name  name
1   Ayşe  Kalem
2   Alex  Defter

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 What does each JOIN need in a multi-join?

  • A shared WHERE
  • A single FROM
  • Its own ON condition Correct answer
  • GROUP BY

Explanation: Each JOIN gets its own ON.

2 Three tables

Question 
FROM orders o
JOIN customers c ON o.customer_id = c.id
____ products p ON o.product_id = p.id;
Answer 
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

Explanation: To add a third table you write another JOIN with its own ON condition.

3 Table aliases: match the terms

Term Meaning
JOIN join
ON condition
alias short name
FROM table

All lessons in Beginner

1 SELECT basics

  1. What is SELECT?
  2. Selecting all columns
  3. Selecting specific columns
  4. Column aliases with AS
  5. Literal values
  6. Summary - SELECT basics

2 WHERE filtering

  1. What is WHERE?
  2. Comparison operators
  3. AND and OR
  4. IN and BETWEEN
  5. Pattern matching with LIKE
  6. Summary - WHERE filtering

3 Ordering and limit

  1. ORDER BY
  2. ASC and DESC
  3. Multi-column ordering
  4. LIMIT
  5. OFFSET
  6. Summary - Ordering and limit

4 Project: CRUD app

  1. Project design
  2. Reading data
  3. Inserting data
  4. Updating data
  5. Deleting data

5 Data types

  1. Integer types
  2. Decimal types
  3. CHAR and VARCHAR
  4. DATE and TIME
  5. BOOLEAN
  6. Summary - Data types

6 Operators

  1. Arithmetic operators
  2. Comparison operators
  3. Logical operators
  4. IS NULL
  5. String concatenation
  6. Summary - Operators

7 String functions

  1. UPPER and LOWER
  2. LENGTH
  3. SUBSTRING
  4. TRIM
  5. CONCAT
  6. Summary - String functions

8 Project: Shopping cart

  1. Cart design
  2. Listing products
  3. Adding to cart
  4. Calculating total
  5. Removing from cart

9 Number functions

  1. ROUND
  2. CEIL and FLOOR
  3. ABS
  4. POWER and SQRT
  5. MOD
  6. Summary - Number functions

10 Date functions

  1. CURRENT_DATE and NOW
  2. EXTRACT
  3. Date arithmetic
  4. DATEDIFF
  5. Date formatting
  6. Summary - Date functions

11 NULL handling

  1. What is NULL?
  2. IS NULL
  3. COALESCE
  4. NULLIF
  5. Three-valued logic
  6. Summary - NULL handling

12 DISTINCT

  1. What is DISTINCT?
  2. DISTINCT on one column
  3. Multi-column DISTINCT
  4. DISTINCT with aggregates
  5. DISTINCT vs GROUP BY
  6. Summary - DISTINCT

13 INSERT

  1. INSERT basics
  2. Inserting into specific columns
  3. Multiple rows
  4. INSERT with SELECT
  5. DEFAULT values
  6. Summary - INSERT

14 UPDATE

  1. UPDATE basics
  2. Conditional UPDATE
  3. Multi-column UPDATE
  4. Calculated updates
  5. UPDATE pitfalls
  6. Summary - UPDATE

15 DELETE

  1. DELETE basics
  2. Conditional DELETE
  3. DELETE vs TRUNCATE
  4. Emptying a table
  5. Safe deletion
  6. Summary - DELETE

16 CREATE TABLE

  1. CREATE TABLE basics
  2. Column definitions
  3. NOT NULL
  4. DEFAULT
  5. PRIMARY KEY
  6. Summary - CREATE TABLE

17 Constraints

  1. UNIQUE constraint
  2. CHECK constraint
  3. FOREIGN KEY
  4. CASCADE
  5. Constraint management
  6. Summary - Constraints

18 Simple JOIN

  1. What is JOIN?
  2. INNER JOIN
  3. The ON clause
  4. Table aliases
  5. Multi-table JOIN
  6. Summary - Simple JOIN

19 Project: Inventory system

  1. Inventory design
  2. Product table
  3. Stock movements
  4. Stock report
  5. Low stock alert

20 Beginner final

  1. Customer query
  2. Order report
  3. Data update
  4. Table redesign