Join types (inner, left, right, full) and aggregate functions with GROUP BY, each explained with a short example.
36 cards · basic cards · AI-written, checked twice. Edit anything.
- What is an INNER JOIN?
- Returns only rows where the join condition is met in both tables
- What is a LEFT JOIN (LEFT OUTER JOIN)?
- Returns all rows from the left table, with matching rows from the right table; NULLs for non-matches
- What is a RIGHT JOIN (RIGHT OUTER JOIN)?
- Returns all rows from the right table, with matching rows from the left table; NULLs for non-matches
- What is a FULL OUTER JOIN?
- Returns all rows from both tables; NULLs for non-matching sides
- Write a basic INNER JOIN
- SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id
- Write a basic LEFT JOIN
- SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id
- Write a basic RIGHT JOIN
- SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id
- Write a basic FULL OUTER JOIN
- SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.user_id
- What is the COUNT() aggregate function?
- Counts rows; COUNT(*) counts all rows, COUNT(column) counts non-null values
- What is the SUM() aggregate function?
- Adds together all values in a numeric column; ignores NULL
- What is the AVG() aggregate function?
- Calculates the average of values in a numeric column; ignores NULL
- What is the MIN() aggregate function?
- Returns the smallest value in a column
- What is the MAX() aggregate function?
- Returns the largest value in a column
- What is GROUP_CONCAT() or STRING_AGG()?
- Concatenates values from multiple rows into a single string
- Does COUNT(*) count NULL values?
- Yes, COUNT(*) counts all rows including any with NULL