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?
Show answer
Returns only rows where the join condition is met in both tables- What is a LEFT JOIN (LEFT OUTER JOIN)?
Show answer
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)?
Show answer
Returns all rows from the right table, with matching rows from the left table; NULLs for non-matches- What is a FULL OUTER JOIN?
Show answer
Returns all rows from both tables; NULLs for non-matching sides- Write a basic INNER JOIN
Show answer
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id- Write a basic LEFT JOIN
Show answer
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id- Write a basic RIGHT JOIN
Show answer
SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id- Write a basic FULL OUTER JOIN
Show answer
SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.user_id- What is the COUNT() aggregate function?
Show answer
Counts rows; COUNT(*) counts all rows, COUNT(column) counts non-null values- What is the SUM() aggregate function?
Show answer
Adds together all values in a numeric column; ignores NULL- What is the AVG() aggregate function?
Show answer
Calculates the average of values in a numeric column; ignores NULL- What is the MIN() aggregate function?
Show answer
Returns the smallest value in a column- What is the MAX() aggregate function?
Show answer
Returns the largest value in a column- What is GROUP_CONCAT() or STRING_AGG()?
Show answer
Concatenates values from multiple rows into a single string- Does COUNT(*) count NULL values?
Show answer
Yes, COUNT(*) counts all rows including any with NULL
FlashKeepers