How Can I Efficiently Retrieve Data from Multiple Tables Using SQL Joins and Unions?

SQL query to return data from multiple tables

Joins and Unions

• Inner Join: Combines rows from two tables based on a common column, returning only matching rows. (See Wikipedia for more info)
• Union: Combines the results of multiple queries, but removes duplicate rows.
• Left Outer Join: Includes all rows from the left table and matching rows from the right table, but omits unmatched rows from the right table.
• Right Outer Join: Includes all rows from the right table and matching rows from the left table, but omits unmatched rows from the left table.
• Intersect: Only returns rows that are identical across multiple tables. (Not supported by all databases)

Examples

Inner Join: Retrieve data from the "cars" and "models" tables:

SELECT
    c.ID,
    m.model
FROM
    cars c
INNER JOIN
    models m
ON
    c.model = m.ID

Union: Combine the results of two queries to get all sports cars and 4WDs:

SELECT
    c.ID,
    m.model
FROM
    cars c
INNER JOIN
    models m
ON
    c.model = m.ID
WHERE
    m.ID = 1
UNION
SELECT
    c.ID,
    m.model
FROM
    cars c
INNER JOIN
    models m
ON
    c.model = m.ID
WHERE
    m.ID = 3

Left Outer Join: Find all brands and their car counts, including brands without any cars:

SELECT
    b.brand,
    COUNT(c.id) AS countOfBrand
FROM
    brands b
LEFT OUTER JOIN
    cars c
ON
    b.ID = c.brand
GROUP BY
    b.brand

Intersects: Retrieve only the rows that are identical across the "colors" and "models" tables:

SELECT
    m.model,
    c.color
FROM
    models m
INNER JOIN
    colors c
ON
    m.ID = c.ID
WHERE
    m.model = 'Sports' AND c.color = 'Red'

The above is the detailed content of How Can I Efficiently Retrieve Data from Multiple Tables Using SQL Joins and Unions?. For more information, please follow other related articles on the PHP Chinese website!