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.
