Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
SQL & Databases · Question Set
Joins Masterclass interview questions for placements and exams.
Questions
15
Included in this set
Subject
SQL & Databases
Explore more sets
Difficulty
Mixed
Level of this set
Go through each question and its explanation. Use this set as a focused practice pack for SQL & Databases.
CROSS JOIN pairs every row from the first input with every row from the second. The result has rows equal to rows(A) times rows(B). Use it to generate combinations such as dates by products, but be careful with size explosion.
SELECT d.dt, p.product_id FROM dates d CROSS JOIN products p;
For complete preparation, combine this set with full subject-wise practice for SQL & Databases. You can also explore other subjects and sets from the links below.
A SELF JOIN uses aliases to treat the same table as two roles. It helps express parent child links or supervisor employee relations. Aliases are mandatory so the columns from each role can be referenced unambiguously.
SELECT e.emp_id, e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON m.emp_id = e.manager_id;
Optimizers try different join orders to minimize intermediate row counts. Starting with the most selective filters reduces data early and speeds the whole plan. Good statistics and appropriate indexes help the optimizer choose correctly. Indexes on join keys enable efficient lookups and hash or merge joins. Composite indexes that match the join and filter columns in order can be especially powerful in star schemas.
SELECT f.sale_id, d.date_id FROM fact_sales f JOIN dim_date d ON d.date_id = f.date_id WHERE d.yyyy_mm = 202509;
An INNER JOIN keeps only the intersection: rows that match on the join keys in both inputs. If a row does not find a partner, it is excluded. Use INNER JOIN when you need strictly related data and do not want NULLs from missing matches.
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
RIGHT JOIN keeps all rows from the right input and matches from the left; LEFT JOIN does the opposite. Swapping table order turns one into the other. Most teams standardize on LEFT JOIN for readability and consistency.
SELECT * FROM A LEFT JOIN B ON B.k = A.k; -- same rows as SELECT * FROM B RIGHT JOIN A ON B.k = A.k;
FULL OUTER JOIN returns the union of matches and non-matches from both sides, padding missing columns with NULLs. It is useful for reconciliation and diff-like reports where you must see orphans from both directions.
SELECT a.id, b.id FROM A a FULL OUTER JOIN B b ON b.id = a.id;
A LEFT JOIN followed by WHERE B.id IS NULL forms an anti-join. It keeps left rows that did not match any right row. NOT EXISTS is an equivalent and often clearer way to write the same logic.
SELECT a.id FROM A a LEFT JOIN B b ON b.id = a.id WHERE b.id IS NULL;
EXISTS returns true on the first found match and stops. It avoids row multiplication and usually produces an efficient semi-join plan. Distinct can work but may add extra sort or hash steps and is less direct.
SELECT a.id FROM A a WHERE EXISTS ( SELECT 1 FROM B b WHERE b.a_id = a.id );
Filters on the right table that go in WHERE will eliminate NULL-extended rows and effectively collapse the join to INNER. Put such filters inside ON so unmatched rows are still preserved. Use WHERE only for predicates that should apply after the join, such as filtering by a left table column.
SELECT c.id, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID' WHERE c.active = 1;
UNION performs a distinct across rows, removing duplicates. UNION ALL appends results and preserves duplicates. Use UNION ALL when you know inputs are disjoint or you want to keep frequency.
SELECT name FROM customers UNION ALL SELECT name FROM employees;
The join expanded each order into one row per item. If you later sum an order level column, it repeats per item and inflates the total. This is a cardinality trap common with one-to-many joins. Fix by aggregating at the right level before joining, or by summing item level amounts instead of order level amounts. Another option is to SELECT DISTINCT at the order level if your goal is only to deduplicate, but pre-aggregation is more precise for sums.
SELECT SUM(oi.line_total) FROM orders o JOIN order_items oi ON oi.order_id = o.id;
NOT EXISTS uses a correlated subquery and returns true when no matching row exists. It is NULL safe and stops at the first match. Plans are often efficient on indexed keys. NOT IN compares a value against a list. If the subquery can return NULL, the comparison becomes unknown and may return no rows at all. Prefer NOT EXISTS for robust results unless you guarantee the subquery column is NOT NULL.
SELECT a.id FROM A a WHERE NOT EXISTS ( SELECT 1 FROM B b WHERE b.a_id = a.id );
Union a LEFT JOIN and a RIGHT JOIN, and remove the overlap by filtering the right side of one branch to NULL. This yields all matches and non-matches from both inputs. The trade-off is extra scan work and potential duplication risk if the filters are not precise. Indexes on join keys help both branches. Test the plan size on large tables to ensure it remains acceptable.
SELECT a.*, b.* FROM A a LEFT JOIN B b ON b.k = a.k UNION ALL SELECT a.*, b.* FROM A a RIGHT JOIN B b ON b.k = a.k WHERE a.k IS NULL;
Use ON to define matching logic and any right-side filters you want applied before deciding whether a row matches. This keeps NULL-extended rows when the right side fails either the join or its filter. Use WHERE for predicates that should apply after the join, typically on the preserved table. A simple rule: put right-side filters in ON when using a LEFT JOIN; put left-side filters in ON when using a RIGHT JOIN.
SELECT c.id, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status='PAID' WHERE c.region='APAC';
LEFT JOIN preserves every row from the left input and fills right-side columns with NULL when a match is missing. Use it to find presence plus absence cases, such as customers without orders.
SELECT c.id, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;