Multi-Table Queries ​

SQL allows us to query data from multiple tables simultaneously. The syntax for querying multiple tables is: SELECT * FROM <table1>, <table2>.

For example, to perform a Cartesian product of the students and classes tables, we can write:

-- FROM students, classes:
SELECT * FROM students, classes;

This query will return a result set that combines every row from the students table with every row from the classes table. The number of rows in the result set is the product of the number of rows in each table. For example, if both tables have 100 rows, the result will have 10,000 rows.

This type of multi-table query is known as a Cartesian query. Caution is required with Cartesian queries, as they can lead to very large result sets—1 billion rows if both tables have 10,000 rows each.

You might also notice that the result set contains duplicate column names, such as two id and two name columns, making it difficult to distinguish them. To address this, we can use aliases for the columns from each table:

-- Set alias:
SELECT
    students.id AS sid,
    students.name,
    students.gender,
    students.score,
    classes.id AS cid,
    classes.name AS cname
FROM students, classes;

When using multi-table queries, it's essential to reference columns using the format table_name.column_name to avoid naming conflicts. To simplify this, SQL allows us to set table aliases, making the query cleaner:

-- Set table alias:
SELECT
    s.id AS sid,
    s.name,
    s.gender,
    s.score,
    c.id AS cid,
    c.name AS cname
FROM students s, classes c;

In this example, s is an alias for the students table, and c is an alias for the classes table.

Multi-table queries can also incorporate WHERE conditions to filter results:

-- Set WHERE clause:
SELECT
    s.id AS sid,
    s.name,
    s.gender,
    s.score,
    c.id AS cid,
    c.name AS cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

In this query, the result set will include only the rows that meet the conditions specified in the WHERE clause, significantly reducing the number of returned records.

Summary ​

Using multi-table queries allows us to retrieve data from multiple tables, potentially generating a large result set (M x N rows). However, it’s essential to be cautious with these queries to avoid overwhelming result sizes. By using column and table aliases, we can clarify the result set and make our queries more readable.