CIS 2200 Joining Data in SQL Notes

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