Multiple Tables Flashcards
Combining tables manually is time-consuming, what easy sequence does SQL provide?
JOIN
What does this statement mean?
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
The SELECT line selects all columns from our combined table
The second line specifies the first table to look into
The third line uses JOIN to combine information from orders to customers
The fourth line tells how to combine two tables. Match the orders tables customer_id column with the customer’s table’s customer_id column.
What syntax is used for JOIN in Tables.
table_name.column_name
Why is table_name.column_name the syntax?
Column names are often repeated across multiple tables so to ensure the requests for columns are unambiguous, thus syntax is used.
How would the query be written if only the orders table’s order_id column and customers table’s customer_name column were specified, instead of selecting all (*) columns?
SELECT orders.order_id
customers.customer_name
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
What is a simple JOIN often referred to as?
INNER JOIN
What is included in the results for a JOIN?
Only the rows that match the ON condition.
C1 C2 C2 C3
A B = B C
Q W != E R
X Y = Y Z
What would be the result for this INNER JOIN?
A B C
X Y Z
What type of JOIN does this represent?
table1.c2 = table2.c2
INNER JOIN
What type of JOIN combines two tables and keep some of the unmatched rows?
LEFT JOIN
What is the result of this Table as LEFT JOIN
C1 C2 C2 C3
A B = B C
Q W != E R
X Y = Y Z
A B C
Q W
X Y Z
The middle rows do not have a match so the final result will keep all rows of the first table but omit and un match row from the second table.
What does a LEFT JOIN do?
Keep all rows from the first table regardless of whether there is a matching row in the second table.
What does this LEFT JOIN statement do?
SELECT *
FROM table1
LEFT JOIN table 2
ON table1.c2 = table2.c2
The first line SELECTs all columns from both tables
The second line SELECTs table1 (the left table)
The third line performs a LEFT JOIN on table2 (right table)
The fourth line tells SQL how to perform the JOIN (by looking for matches in column c2)
What type of JOIN is used to combine all rows of one table with all rows of another table?
CROSS JOIN
What does this CROSS JOIN statement do?
SELECT shirts.shirt_color,
pants.pants_color
FROM shirts
CROSS JOIN pants;
The first line select the columns shirt_color and pants_color
The third line pulls data from the Table shirts
The fourth line performs a CROSS JOIN with pants.
What type of JOIN does NOT require an ON statement?
CROSS JOIN
The ON statement for INNER JOIN and LEFT JOIN is used for what?
Joining columns!
What type of operator stacks one dataset on top of the other?
UNION
The UNION operator deals with how many tables?
Two
What does this UNION statement do?
table1: table2:
pokemon type pokemon type
Bulbasaur Grass Snorlax Normal
Charmander Fire
Squirtle Water
SELECT *
FROM table1
UNION
SELECT *
FROM table2;
pokemon type
Bulbasaur Grass
Charmander Fire
Squirtle Water
Snorlax Normal
What are some rules regarding a UNION?
Tables must have the same number of columns
The columns must have the same data types in the same order as the first Table.
What type of statement is WITH?
Allows us to perform separate query
What does this WITH statement do?
WITH previous_results AS (
SELECT ….
….
….
)
SELECT *
FROM previous_results
JOIN customers
ON _______ = ________;
The WITH statement allows us to perform a separate query
previous_results is the alias that we will use to reference any columns from the query inside of the WITH clause
How does the WITH statement work?
Putting the first query inside the parentheses ( ) and giving it a name.
After, we can use the name as if its a table and write a new query using the first query.
