Core CS · DBMS

A query returns a table, so put it where a table goes

Set operators stack two queries, a subquery drops one inside another, a derived table stands one where a table name belongs, and a view gives one a name. Four features, one idea. You will also watch a correct-looking query return nothing at all, because one row held a NULL.

Watch NOT IN return zero rows →

01 The idea

Four features that turn out to be one feature

Every SELECT you have written so far produced a table. Not a list, not a print-out: a set of rows with named columns, the same kind of thing that student and applied are. That is worth saying out loud, because once a query result is a table, it can go anywhere a table can go.

Stack two of them and you have a set operation: UNION, INTERSECT, EXCEPT. Put one inside the WHERE of another and you have a subquery. Put one in the FROM clause where a table name would normally sit and you have a derived table. Give one a name and you have a view. Four sections of the syllabus, one sentence underneath all of them.

The reason this lesson is longer than “here is the syntax” is that two of these have a trap that costs marks and costs production systems real money. The first is that removing duplicates is not free, so UNION and UNION ALL are not two spellings of the same operator. The second is NULL. A subquery whose column contains a single NULL can turn a perfectly reasonable NOT IN into a query that returns zero rows and reports no error, and you will watch that happen in section 04.

A SELECT returns a table, so a SELECT can go wherever a table goes: stacked with a set operator, nested in a WHERE, standing in a FROM, or stored under a name as a view. Learn the one idea and the four features stop being four things to memorise.

Union compatibleTwo results that can be stacked. They need the same number of columns, and column types the database can line up, matched left to right by position. The names are never compared, and the result takes its names from the first SELECT.

SubqueryA SELECT written inside another statement, in parentheses. Where you put it decides what it is allowed to return: exactly one value, one column of many rows, or a whole table.

Correlated subqueryA subquery that mentions a column belonging to the query it sits inside. It cannot be run once and reused, because its answer changes with every outer row, so in the plain reading it runs again for each of them.

02 Worked example

Who applied, and was shortlisted nowhere

One placement cell, three tiny tables, and every query in this lesson runs against them. Four students, four applications, two shortlists. Copy them onto paper now, because you are going to be asked to compute results by hand and the whole point is that you can.

-- student  sid   name     dept   mentor   cgpa  S1    Asha     CSE    NULL     8.9      -- Asha is the senior-most, so she has no mentor  S2    Bhavna   CSE    S1       7.4  S3    Chetan   ECE    S1       9.2  S4    Divya    ECE    S2       6.8 -- applied (sid, company)              -- shortlist (sid, company)  S1    Infosys                          S1    Infosys  S1    TCS                              S3    TCS  S2    Infosys  S3    TCS                          -- shortlist is always a subset of applied

Two things to notice before any SQL. The mentor column on line 3 holds one NULL, and it is there for an ordinary reason: somebody has to be the most senior. And applied and shortlist both have a sid column, so the sid values coming out of each of them are two results of the same shape, which is exactly the condition for stacking them.

Now the placement officer asks one question: which students applied somewhere and were shortlisted nowhere? Watch it get built four different ways, each one a different place to put a SELECT.

1 · Two listsSELECT sid FROM applied gives 4 rows, SELECT sid FROM shortlist gives 2. One column each, same type

→

2 · Stack themUNION ALL returns 6 rows and UNION returns 3, because UNION throws the duplicates out of the combined result

→

3 · Subtract themapplied EXCEPT shortlist returns exactly one row, S2. Bhavna applied to Infosys and was shortlisted nowhere

→

4 · Nest it insteadWHERE sid NOT IN (SELECT sid FROM shortlist) gives the same S2, as a subquery rather than a set operation

→

5 · Name itCREATE VIEW not_shortlisted AS ... stores the shortlisted-nowhere half of the test under a name, so the placement cell types one word

Node 3 is the highlighted one because it is the shortest correct answer and because of what it quietly does. EXCEPT compares the distinct sid values of the left result against the distinct sid values of the right result. Left holds S1, S1, S2, S3, which is three distinct values. Right holds S1 and S3. Take those two away and S2 is what is left. One row, and you can check it on paper in ten seconds.

Node 4 gives the same single row here, and that agreement is the trap this lesson is built around. The two nodes stop meaning the same thing the moment the right-hand column is allowed to hold a NULL. Node 3 keeps working, because EXCEPT drops a left row only when the right side genuinely contains it, and a NULL on the right is not S2. Node 4 returns nothing at all, silently. The mentor column on line 3 already holds exactly that kind of NULL, so section 04 points a NOT IN at that column and you watch every row disappear, one student at a time.

03 Mechanics

Three shapes, and the one thing that trips each

A set operator stacks two finished results. A subquery puts one query inside another. A view gives a query a name. Each has exactly one thing that catches people out, and this section is those three things, in order, against the tables from section 02.

Set operators: the duplicate removal is the cost

All four take a whole result on the left and a whole result on the right. Here they are against SELECT sid FROM applied, which produces S1, S1, S2, S3, and SELECT sid FROM shortlist, which produces S1 and S3.

Operator	What comes back	Rows	What it costs
UNION ALL	`S1, S1, S2, S3, S1, S3`	`6`	Nothing extra. The rows of both sides are handed straight through, in no guaranteed order.
UNION	`S1, S2, S3`	`3`	`a sort or a hash of the whole combined result, to find the duplicates`
INTERSECT	`S1, S3`	`2`	`the same duplicate removal, plus matching every row against the other side`
EXCEPT (Oracle: MINUS)	`S2`	`1`	`the same again, and the order of the two sides changes the question`

Three of the four deduplicate, and that is the sentence to carry into an interview. The database cannot know a row is a duplicate without comparing it to every other row, so it sorts or hashes the whole result first. UNION ALL skips all of that and streams both inputs unchanged. On four rows this is invisible; on four million it is the difference between a query you can run and one you cannot. Write UNION ALL unless you can name the duplicate you are removing.

The three deduplicating operators each have an ALL form in standard SQL, so INTERSECT ALL and EXCEPT ALL exist alongside UNION ALL and keep duplicates by counting them. You will rarely see them; UNION ALL is the one that made it into everyday use.

A set operator compares whole rows, not the first column. Every query above selected sid alone, so “the same row” meant “the same student”. Select sid, company on both sides instead and the same operator asks about the same student at the same company, which is a different question with a different answer.

Union compatible. The database checks two things and nothing else: the two sides must return the same number of columns, and the columns must be type-compatible position by position, left to right. It never looks at names, so two columns that mean entirely different things will stack happily as long as their types line up. The result takes its column names from the first SELECT, and a set operation may carry one ORDER BY, written once at the very end, naming only the columns of that result.

Subqueries: the position decides how many rows are allowed

A subquery is a SELECT in parentheses inside another statement. There is no separate keyword for the different kinds. What makes a subquery scalar or multi-row is only where you wrote it, and getting that wrong is a run-time error rather than a syntax error.

-- 1 · scalar subquery in the SELECT list: one row and one column, or NULLSELECT s.name,       (SELECT l.company FROM shortlist l WHERE l.sid = s.sid) AS shortlisted_atFROM   student s;--  Asha Infosys | Bhavna NULL | Chetan TCS | Divya NULL -- 2 · single-row subquery with = : more than one row back is a run-time errorSELECT name FROM studentWHERE  sid = (SELECT sid FROM shortlist WHERE company = 'TCS');   -- Chetan -- 3 · multi-row subquery with IN: one column, any number of rowsSELECT name FROM studentWHERE  sid IN (SELECT sid FROM shortlist);              -- Asha, Chetan

Line 3 is both scalar and correlated: it mentions s.sid, a column of the outer query, so it runs once per student and returns that student’s company. Bhavna and Divya have no shortlist row, so for them the subquery returns no rows at all, and a scalar subquery that returns no rows evaluates to NULL. Not zero, not an error, not an empty string. That is the quiet half of the NULL story.

Line 9 is correct today and fragile tomorrow. Exactly one shortlist row has company = 'TCS', so the subquery yields S3 and the outer query returns Chetan. Point the same shape at applied with company = 'Infosys' and the subquery yields S1 and S2, and the statement fails with a message like more than one row returned by a subquery used as an expression. It does not pick one. Point it at a company nobody applied to and it does not fail either: the subquery is NULL, sid = NULL is unknown, and you get an empty result with nothing to tell you why.

Line 13 is the multi-row form. IN is not a special keyword: x IN (subquery) is exactly x = ANY (subquery), and x NOT IN (subquery) is exactly x <> ALL (subquery). ANY means the comparison holds against at least one value the subquery produced; ALL means it holds against every one of them. So cgpa > ALL (SELECT cgpa FROM student WHERE dept = 'CSE') asks for a CGPA above every CSE CGPA, which on this data means above 8.9, and it returns Chetan alone.

The fourth position is the FROM clause. A query written there is a derived table, and it is the position that makes the “a query is a table” claim undeniable, because you are literally using one where a table name belongs.

-- 4 · derived table: a whole query standing where a table name goesSELECT name, appsFROM   (SELECT s.name       AS name,               COUNT(a.sid) AS apps        FROM   student s        LEFT JOIN applied a ON a.sid = s.sid        GROUP BY s.sid, s.name) AS tWHERE  apps = 0;                          -- Divya-- inner result:  Asha 2 | Bhavna 1 | Chetan 1 | Divya 0

Read line 4 carefully, because it is the most-missed line in SQL papers. The LEFT JOIN gives Divya one row with every applied column set to NULL. COUNT(a.sid) counts non-NULL values, so it counts that row as nothing and Divya gets 0. COUNT(*) counts rows, so it would count the very same row as 1, Divya would score 1, and line 8 would return nothing at all. Same join, same data, one character of difference, opposite answers.

Give a derived table an alias. MySQL rejects one without an alias outright, and Oracle is the vendor that has never required it. Being honest about this example: HAVING COUNT(a.sid) = 0 would do the same job here in one less layer. A derived table earns its place when you need to join an aggregate back to another table, or filter on something HAVING cannot see, and the hard problem at the end of this lesson is exactly that case.

Views: a stored question, never a stored answer

A view is a query with a name. That is the entire definition, and almost every wrong answer about views comes from imagining that something was copied.

CREATE VIEW not_shortlisted ASSELECT s.sid, s.name, s.deptFROM   student sWHERE  NOT EXISTS (SELECT 1 FROM shortlist l WHERE l.sid = s.sid); SELECT name FROM not_shortlisted WHERE dept = 'ECE';   -- Divya-- the view holds Bhavna and Divya; dept = 'ECE' narrows that to Divya-- nothing was stored except the text on lines 1 to 4

Notice the view answers a slightly wider question than section 02 asked. It keeps the “shortlisted nowhere” half and drops the “applied somewhere” half, so Divya, who never applied at all, is in it alongside Bhavna. Add AND EXISTS (SELECT 1 FROM applied a WHERE a.sid = s.sid) and you have the section 02 question, named.

Line 6 does not read a saved table. The database expands the view back into the query on lines 2 to 4, attaches dept = 'ECE' to it, and plans the whole thing as one statement. So a view is not faster than the query it wraps, and it is not an index. What it gives you is a name, one place to change the definition when the schema moves under you, and the ability to grant somebody three columns of a table instead of all of them.

Which views you can write through. A view is updatable when the database can trace each of its rows back to exactly one row of exactly one base table. In practice: one table in FROM, plain columns rather than expressions, and no DISTINCT, GROUP BY, aggregate or set operator. Group the rows and there is no single row to write to, so the database refuses. Some systems will accept a join view when the write touches only one key-preserved table, but the strict answer is the one to give. When you need writes through a view that fails the test, you attach an INSTEAD OF trigger and make the decision yourself. And WITH CHECK OPTION, added at create time, makes the database reject a write that would produce a row the view can no longer see.

One sentence on the other kind. A materialised view really does store its result on disk, so reading it is a table scan instead of a re-run of the query, and the price is that the rows are exactly as old as the last REFRESH.

Set operators need two results of the same shape and pay for every duplicate they remove. A subquery’s position decides how many rows it may return. A view stores the question and never the answer. Everything else in this lesson is a consequence of one of those three sentences.

05 Cheat sheet

The whole module on one card

Only the rows that get asked. Read the right-hand column first on the morning of an interview, because that is the column that separates someone who has written these from someone who has read about them.

Construct	What it does	The thing that trips people
UNION ALL	stacks both results and keeps every duplicate	`the cheap one; write this unless you can name the duplicate you are removing`
UNION	stacks both results and removes duplicates	`pays for a sort or hash of the whole combined result`
INTERSECT	whole rows present in both sides, deduplicated	`not an inner join: a join multiplies duplicate rows, INTERSECT collapses them`
EXCEPT / MINUS	rows of the left side that are not in the right, deduplicated	`swapping the two sides is a different question; Oracle spells it MINUS`
union compatibility	the only thing checked before a set operation runs	`same column count, types matched by position; names ignored, result named from the first SELECT`
set operators and NULL	duplicate removal treats two NULLs as the same value	`the exact opposite of =, which never matches a NULL to anything`
scalar subquery	one row, one column, used where a value goes	`two rows back is a run-time error; no rows back is NULL and no error at all`
IN / ANY / ALL	compare one value against a column of many	`IN is = ANY; NOT IN is <> ALL; over an empty subquery ALL is true for every row and ANY is false for every row`
NOT IN	value absent from the subquery’s column	`one NULL anywhere in that column and the query returns zero rows, silently`
EXISTS / NOT EXISTS	asks only whether any row came back	`NULL-safe, because EXISTS is only ever true or false and never UNKNOWN; the inner SELECT list is ignored, so SELECT 1 is conventional`
correlated subquery	mentions an outer column, so it re-runs per outer row	`cannot be evaluated once; planners often rewrite it to a semi-join, but reason about it as one run per row`
derived table	a query in FROM, where a table name would go	`MySQL rejects it without an alias; use it to filter or join on an aggregate`
CREATE VIEW	stores the text of a query under a name	`no rows are stored, so it is never faster; reading it re-runs the query`
updatable view	a view you can INSERT, UPDATE or DELETE through	`needs one base table and plain columns; GROUP BY, aggregates, DISTINCT or a set operator make it read-only`
MATERIALIZED VIEW	stores the actual rows, refreshed on command	`data is as old as the last REFRESH; PostgreSQL and Oracle have them, MySQL does not`

The dedup is the whole costThree of the four set operators cannot call a row unique until they have seen every other row, so the database sorts or hashes the entire combined result before it can return anything at all. UNION ALL does none of that and streams both inputs unchanged. This is the single most useful thing you can say about set operators in an interview.

NOT EXISTS is the anti-join to type by defaultNOT IN is correct only while the subquery’s column has no NULLs, and nothing in the query records that assumption. NOT EXISTS is correct either way, costs nothing extra on a modern planner, and does not quietly change meaning the day somebody drops a NOT NULL constraint.

Empty and NULL are different failuresA subquery that returns no rows and one that returns a NULL behave in opposite ways. x NOT IN (empty) is true for every row; x > ALL (empty) is true for every row and x > ANY (empty) is false for every row. Put one NULL in that same column and the comparisons become UNKNOWN instead, and UNKNOWN is not true, so the row is dropped.

06 Where & why

The same four ideas, four different spellings

Nothing in this lesson is a diagram from a textbook. Every keyword here is one you can type into a database that is running right now, and the differences between the four below are the ones interviewers reach for when they want to see whether you have actually used one.

PostgreSQL

All four operators, and views that are rewrite rules

It has UNION, INTERSECT and EXCEPT, each with its own ALL form, so all six spellings are available. A simple view is automatically updatable, and anything past that needs an INSTEAD OF trigger. EXPLAIN is where the subquery material stops being theory: an uncorrelated IN usually turns into a hash semi-join, while a correlated subquery shows up as a SubPlan that runs per row. Materialised views are refreshed by hand with REFRESH MATERIALIZED VIEW.

Oracle

MINUS, and materialised views that rewrite your query

Oracle’s name for EXCEPT is MINUS, and MINUS is the word an interviewer will use, so recognise both. An inline view in FROM does not need an alias here, which is the one place the strict rule you learned is a vendor rule. Its materialised views grew out of the old snapshot feature and can refresh on commit or on a schedule, and with query rewrite enabled the optimiser may answer a query written against the base tables from the materialised view without you naming it.

MySQL

The one that made everybody write it by hand

MySQL had UNION and UNION ALL for most of its life and no INTERSECT or EXCEPT at all; they arrived only in the 8.0 series, so an enormous amount of working MySQL code writes those two as a join and a NOT EXISTS instead. A derived table must carry an alias. There are no materialised views, so people build one by hand as a real table plus a scheduled job, which is a good way to learn what a materialised view actually is. It also refuses to UPDATE a table while a subquery in the same statement reads it, error 1093, and the usual workaround is wrapping that subquery in a derived table.

SQLite

Every view is read only, and it says so

SQLite supports all four set operators, and its views are always read-only: no INSERT, UPDATE or DELETE reaches the base table unless you write an INSTEAD OF trigger yourself. There are no materialised views. It is the clearest demonstration of what a view is, because SQLite never pretends otherwise. The view is the text of a SELECT and nothing else, which is exactly what the other three store too.

The interview answer is never the vendor. It is the shape underneath: a set operator needs two results with the same shape and pays to remove duplicates, a subquery that mentions an outer column has to run again for every outer row, and a view is a stored question. Every difference on this page is spelling on top of those three sentences.

07 Interview questions

What they actually ask

Set operations and subqueries are where a DBMS interview stops testing recall and starts testing whether you can predict what a statement returns. Two of the questions below are the ones candidates lose on most: the difference between UNION and UNION ALL answered without mentioning cost, and NOT IN answered without mentioning NULL.

What is the difference between UNION and UNION ALL, and which do you write?

UNION removes duplicate rows from the combined result and UNION ALL does not. Removing them is not free: the database has to sort or hash the entire combined result to find duplicates, so UNION ALL is the cheaper operator and the one to reach for by default. Write plain UNION only when you can say out loud which duplicate you are removing and why it should not be in the answer.

Two SELECTs, and the database rejects the UNION. What is it actually checking?

Two things, and nothing more: the two sides must return the same number of columns, and those columns must be type-compatible position by position, left to right. Names are never compared, so two columns that mean completely different things will stack happily as long as their types line up, which is how a union produces confident nonsense. The result takes its column names from the first SELECT, and a set operation carries at most one ORDER BY, written once at the very end.

Is INTERSECT the same as an inner join?

No. INTERSECT compares whole rows and deduplicates, so each matching row comes back once; an inner join pairs rows up, so a value appearing twice on one side and three times on the other gives you six rows. A join can also return columns from both tables, which a set operator cannot, because both sides have to be the same shape. There is a third difference worth knowing: the set operators treat two NULLs as the same value when deciding what counts as a duplicate, while a join condition using = never matches a NULL to anything.

What is a subquery, and where can one sit?

A SELECT written inside another statement, in parentheses. Three positions matter. In the SELECT list it must produce exactly one row and one column and is called a scalar subquery. In WHERE, an = demands one row while IN, ANY, ALL and EXISTS accept many. In FROM it produces a whole table and is called a derived table. Position is the only thing that decides how many rows it may return, and getting it wrong fails at run time rather than at parse time.

What is a correlated subquery, and how do you spot one?

A subquery that refers to a column of the query it is written inside. You spot it by looking for an outer table alias in the inner query: if the inner SELECT mentions s.sid and s is the outer table, it is correlated. The consequence is that it cannot be evaluated once and reused, because its answer changes with every outer row, so the meaning you reason about is one run per outer row.

IN or EXISTS, which one is faster?

On a modern optimiser, usually neither. An uncorrelated IN and a correlated EXISTS are typically rewritten into the same semi-join, and the plan is chosen from table statistics rather than from which keyword you typed. The old rule of thumb, EXISTS for a large inner table and IN for a small one, dates from planners that did not do that rewrite. The difference that has not gone away is correctness: NOT IN and NOT EXISTS do not mean the same thing once the inner column can hold a NULL.

A NOT IN query returns zero rows and you were certain it should return some. What is wrong?

The subquery’s column contains a NULL. NOT IN expands into a chain of <> comparisons joined by AND, and comparing anything to NULL gives UNKNOWN rather than true or false, so every row that does not match some other value in the list ends up UNKNOWN, and WHERE keeps only rows that are true. Three fixes: rewrite it as NOT EXISTS, add IS NOT NULL inside the subquery, or use a LEFT JOIN with IS NULL on the joined key. The first is the one to reach for, because it stays right whether or not anyone ever makes that column nullable.

A subquery in the SELECT list came back with two rows. What happens?

The statement fails at run time with a message along the lines of “more than one row returned by a subquery used as an expression”. The database does not pick one for you. That is the dangerous part: the query is correct on today’s data and fails the day a second matching row is inserted, so it passes review and breaks in production. If the same subquery returns no rows it does not fail at all, it evaluates to NULL, which is quieter and often worse.

What does CREATE VIEW actually store, and is a view faster than the query?

It stores the text of a query and nothing else. No rows are copied, so a view cannot be faster than the statement it wraps: reading from it expands it back into that query, attaches whatever WHERE you added, and plans the whole thing as one statement. What you buy is a name, one place to change the definition when the schema moves, and the ability to grant somebody a slice of a table instead of all of it.

Can you INSERT or UPDATE through a view?

Only when the database can trace each row of the view back to exactly one row of exactly one base table. One table in FROM, plain columns and a WHERE clause is fine. A GROUP BY, an aggregate, a DISTINCT or a set operator is not, because there is no single row to write to. When you need writes through a view that fails that test you attach an INSTEAD OF trigger and make the mapping yourself. SQLite is the strict case: its views are read-only, always.

What is a materialised view, and when is it worth it?

Hard

Build a single view company_funnel that returns one row per company appearing in applied, with three numbers: how many students applied there, how many of those were shortlisted there, and how many of those were shortlisted nowhere at all. Give the two rows it returns on the section 02 data.

Follow-up

The third number counts students against a table the grouping never reaches, so it cannot be one more column on the same GROUP BY. One of the three pieces has to be an anti-join, and it has to be the form that keeps working if shortlist.sid is ever made nullable.

Show the hint

Count each of the three numbers on its own first, keyed by company, and then ask where in a single statement three separate results can be brought back together.