The Join Clause Flashcards
(12 cards)
What is the purpose of JOIN clauses in SQL?
JOINs let us combine tables (more precisely rows from those tables) based on matches between columns that are related.
How does INNER JOIN work and what does the resulting table contain?
INNER JOIN takes 2 tables and creates a new one containing all columns from both tables. It includes ONLY the rows where values for the specified related columns match.
Write the syntax for an INNER JOIN that connects customers and orders tables on customer_id.
SELECT columns FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
Explain how LEFT JOIN works and what happens when there are no matches.
LEFT JOIN includes ALL rows from the left table (first mentioned). All columns from both tables are included. Matching rows fill right table columns with data; non-matching rows fill right table columns with NULL.
Write a LEFT JOIN query to show all employees and their department names (if they have one).
SELECT column1, column2, … FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
How does RIGHT JOIN differ from LEFT JOIN?
RIGHT JOIN includes ALL rows from the right table (second mentioned). Left table columns are filled with matching data where matches exist, and with NULL where no matches exist.
If you have Customers LEFT JOIN Orders vs Customers RIGHT JOIN Orders, what’s the difference in results?
LEFT JOIN: Shows all customers (even those with no orders, orders columns = NULL). RIGHT JOIN: Shows all orders (even orphaned ones, customer columns = NULL for unmatched orders).
Describe the 4-step process for creating LEFT/RIGHT JOIN results.
- Include all rows from the priority table (left/right depending on join type), 2. Add columns from the other table, 3. Fill other table’s columns with matching values where matches exist, 4. Fill remaining positions with NULL.
You want to see all products and their sales data, including products that haven’t been sold. Which JOIN type and why?
LEFT JOIN with products as the left table. This ensures all products appear in results, with sales columns showing NULL for unsold products.
In a LEFT JOIN between employees and departments, some employees show NULL for department_name. What does this indicate?
Those employees have no matching department_id in the departments table - they either have NULL department_id or their department_id doesn’t exist in the departments table.
You have Authors (author_id, name) and Books (book_id, title, author_id). Compare the results of INNER JOIN vs LEFT JOIN.
INNER JOIN: Only authors who have written books. LEFT JOIN: All authors, with book info for those who have books and NULL book columns for authors with no books.
Design a query to show all customers and their total order count, including customers who never placed orders (show 0).
SELECT c.customer_name, COUNT(o.order_id) as order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name;
