All sample questions / SQL

What does SELECT stations.label, shifts.label FROM stations CROSS JOIN shifts ... return? SQL Joins

Block 2, Joins, question 1 of the SQL sample

CREATE TABLE stations (label TEXT);
INSERT INTO stations VALUES
  ('Gate1'),
  ('Gate2'),
  ('Gate3');
CREATE TABLE shifts (label TEXT);
INSERT INTO shifts VALUES
  ('Morning'),
  ('Evening');

SELECT stations.label, shifts.label FROM stations CROSS JOIN shifts ORDER BY stations.label, shifts.label

What does this query return?

  1. A. Gate1 | Morning Gate2 | Evening
  2. B. (no rows)
  3. C. Gate1 | Evening Gate1 | Morning Gate2 | Evening Gate2 | Morning Gate3 | Evening
  4. D. Gate1 | Evening Gate1 | Morning Gate2 | Evening Gate2 | Morning Gate3 | Evening Gate3 | Morning
Show the answer and why each option is right or wrong

Answer, D. Gate1 | Evening Gate1 | Morning Gate2 | Evening Gate2 | Morning Gate3 | Evening Gate3 | Morning

A. Gate1 | Morning Gate2 | Evening
This pairs the tables row by row as if zipping two lists together, which only produces as many rows as the shorter table and drops Gate3 and most of the combinations entirely.
B. (no rows)
This assumes a CROSS JOIN needs some equality condition to link the tables, but a CROSS JOIN has no condition at all, every row of one side pairs with every row of the other regardless of any matching column.
C. Gate1 | Evening Gate1 | Morning Gate2 | Evening Gate2 | Morning Gate3 | Evening
This undercounts the result as if it only had three stations plus two shifts minus one, five rows, instead of the full product of three times two, six rows, and it drops Gate3 with Morning entirely.
D. Gate1 | Evening Gate1 | Morning Gate2 | Evening Gate2 | Morning Gate3 | Evening Gate3 | Morning
Correct. CROSS JOIN pairs every station with every shift, three stations times two shifts gives six combinations in total.

A CROSS JOIN always produces rows equal to the product of the two row counts, three stations and two shifts multiply to six, never a sum and never a row by row pairing that stops at the shorter table.

The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.

This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.

Get the 502 SQL questions, 9 euros

Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros

Or play the free SQL sample, twenty questions in the browser, also as a free PDF.

Next question

Did this save you a lookup? Buy me a coffee on Ko-fi.