Join Clause Flashcards
What does the JOIN clause do in SQL?
It combines rows from two or more tables based on related columns.
What is an INNER JOIN?
It returns only the rows where there is a match in both joined tables.
What is the syntax for INNER JOIN?
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
What is a LEFT JOIN?
It returns all rows from the left table, and matched rows from the right table; unmatched rows from the right table are filled with NULL.
What is the syntax for LEFT JOIN?
SELECT columns FROM left_table LEFT JOIN right_table ON left_table.column = right_table.column;
What is a RIGHT JOIN?
It returns all rows from the right table, and matched rows from the left table; unmatched rows from the left table are filled with NULL.
What is the syntax for RIGHT JOIN?
SELECT columns FROM left_table RIGHT JOIN right_table ON left_table.column = right_table.column;
Which JOIN returns only rows with matches in both tables?
INNER JOIN.
Which JOIN returns all rows from the left table?
LEFT JOIN.
Which JOIN returns all rows from the right table?
RIGHT JOIN.
If you want to show all customers and their orders, including customers with no orders, which JOIN do you use?
LEFT JOIN.
If you want to show only customers who have placed orders, which JOIN should you use?
INNER JOIN.
If you want to display all orders, even if they are not linked to a customer, which JOIN do you use?
RIGHT JOIN (assuming orders table is the right table).
Spot the mistake: ‘SELECT * FROM orders INNER JOIN customers orders.customer_id = customers.id;’
Missing ON keyword. Correct syntax: ON orders.customer_id = customers.id;
Spot the mistake: ‘RIGHT JOIN SELECT * FROM table1 table2 ON condition;’
Incorrect syntax. Correct: SELECT * FROM table1 RIGHT JOIN table2 ON condition;
What is the first step in a JOIN operation?
Form the Cartesian product of the two tables.
What happens after the Cartesian product is formed in an INNER JOIN?
Filter the rows where the specified columns match.
What happens to unmatched rows in a LEFT JOIN?
They are included, with NULL values for columns from the right table.
What happens to unmatched rows in a RIGHT JOIN?
They are included, with NULL values for columns from the left table.
If a LEFT JOIN is used and there are no matches in the right table, what is returned?
Rows from the left table with NULLs in the right table columns.
If you mistakenly use INNER JOIN instead of LEFT JOIN, what data might you lose?
Records from the left table that have no matching row in the right table.
Explain in your own words the difference between INNER JOIN and LEFT JOIN.
INNER JOIN returns only matching rows from both tables, while LEFT JOIN returns all rows from the left table and fills NULLs for unmatched right table rows.
Describe a situation where you would prefer LEFT JOIN over INNER JOIN.
When you want to include all records from the left table, even if they have no matches in the right table.
What is the difference between LEFT JOIN and RIGHT JOIN?
LEFT JOIN includes all rows from the left table, RIGHT JOIN includes all rows from the right table, with NULLs for unmatched rows from the opposite table.
