SQL Joins SQL Topic exerciseEasyVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

CROSS JOIN — Every Department × Every Job (Cartesian Product)

Generate the intentional Cartesian product of every department and every job.

  • Joins
  • Sorting

Exercise brief

Understand the request

Workforce modelling analyst Scenario planning needs the complete catalog of possible department and job combinations.

Generate every possible department × job combination — the Cartesian product. With 5 departments and 6 jobs, the result MUST be exactly 30 rows. Return department_id, department_name, job_id, job_title — ordered by department_id, job_id.

Return

  • Return department_id, department_name, job_id, job_title in this exact left-to-right order.

Constraints

  • Use explicit CROSS JOIN.
  • Do not use an accidental comma join.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

jobs

  • job_idVARCHAR(20)
  • job_titleVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

CROSS JOIN has NO ON clause — every left row pairs with every right row.

Hint 2

Result row count = (left rows) × (right rows). Always sanity-check with `SELECT COUNT(*) FROM left, COUNT(*) FROM right` first.

Hint 3

Some engines accept comma-separated FROM (`FROM a, b`) as an implicit CROSS JOIN — but explicit `CROSS JOIN` is much clearer.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT d.department_id, d.department_name, j.job_id, j.job_title FROM departments d CROSS JOIN jobs j ORDER BY d.department_id, j.job_id;

Why this works

CROSS JOIN's job is the Cartesian product. It feels weird at first — most queries don't want it — but it shines for filling reporting matrices (every dept × every month, every product × every region, etc.) where you need empty cells to appear.

Success check

Every department-job combination appears once in deterministic key order.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_iddepartment_namejob_idjob_title
10ITFIN_ANALYSTFinancial Analyst
10ITHR_REPHR Representative
10ITIT_MGRIT Manager
10ITIT_PROGSoftware Developer
10ITMKT_MGRMarketing Manager
10ITSALES_REPSales Representative
20HRFIN_ANALYSTFinancial Analyst
20HRHR_REPHR Representative
20HRIT_MGRIT Manager
20HRIT_PROGSoftware Developer

Previewing 10 of 30 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.