Joining Data in SQL
We use joins to combine multiple tables within a query.
The ON statement tells the SQL engine what columns to use to join the tables.
Joins come after the FROM clause.
• Inner Join is the most common way to join data using SQL. An inner join includes only rows that have a match as specified
by the ON clause.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
• Left Join includes all rows from an inner join, plus any rows from the first table that don't have a match in the second table.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
• Right Join includes all rows from the second table that don't have a match in the first table + any rows from an inner join.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
• Full Outer Join includes all rows from both joined tables.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
SQLite doesn't support full outer joins or right joins.
Joins with Subqueries
, Subqueries can be used to substitute parts of queries, allowing us to find the answers to more complex questions.
We can also join to the result of a subquery, just like we could a table.
The result of a subquery is calculated first, so we read
from the inside out.
This simple query selects all columns from cities, filtering rows that are marked as capital cities by having a value
for capital of 1.
The INNER JOIN joins the subquery result, aliased as c, to the facts table based on the ON clause.
Two columns are selected from the results of the join:
o f.name, aliased as country
o c.name, aliased as capital_city
The results are limited to the first 10 rows.
Joining 3 Tables
• A schema diagram helps us understand the available columns and the
structure of the data. In a schema diagram, relationships are shown using
lines between tables.
• Each row's primary key must be unique.
Exercise: Write a query that gathers data about the invoice with an invoice_id of 4. Include the following columns in
order:
o The id of the track, track_id
o The name of the track, track_name
o The name of the media type of the
track, track_type
o The price that the customer paid for the
track, unit_price
o The quantity of the track that was
purchased, quantity
Joining 3+ Tables
We use joins to combine multiple tables within a query.
The ON statement tells the SQL engine what columns to use to join the tables.
Joins come after the FROM clause.
• Inner Join is the most common way to join data using SQL. An inner join includes only rows that have a match as specified
by the ON clause.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
• Left Join includes all rows from an inner join, plus any rows from the first table that don't have a match in the second table.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
• Right Join includes all rows from the second table that don't have a match in the first table + any rows from an inner join.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
• Full Outer Join includes all rows from both joined tables.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
SQLite doesn't support full outer joins or right joins.
Joins with Subqueries
, Subqueries can be used to substitute parts of queries, allowing us to find the answers to more complex questions.
We can also join to the result of a subquery, just like we could a table.
The result of a subquery is calculated first, so we read
from the inside out.
This simple query selects all columns from cities, filtering rows that are marked as capital cities by having a value
for capital of 1.
The INNER JOIN joins the subquery result, aliased as c, to the facts table based on the ON clause.
Two columns are selected from the results of the join:
o f.name, aliased as country
o c.name, aliased as capital_city
The results are limited to the first 10 rows.
Joining 3 Tables
• A schema diagram helps us understand the available columns and the
structure of the data. In a schema diagram, relationships are shown using
lines between tables.
• Each row's primary key must be unique.
Exercise: Write a query that gathers data about the invoice with an invoice_id of 4. Include the following columns in
order:
o The id of the track, track_id
o The name of the track, track_name
o The name of the media type of the
track, track_type
o The price that the customer paid for the
track, unit_price
o The quantity of the track that was
purchased, quantity
Joining 3+ Tables
