To find values present in both results, put INTERSECT between two SELECT statements. The intersection of SQL query results contains the rows that appear on both sides; start with a small example and inspect each input separately.

INTERSECT SQL results with a working example

Imagine two lists of customer IDs: people who attended a workshop and people who downloaded its materials. To intersect SQL results, select the same kind of value from each list. This PostgreSQL example is self-contained, so you can paste it into a query console without creating tables:

WITH attendees(customer_id) AS (
  VALUES (1), (2), (2), (3)
),
downloaders(customer_id) AS (
  VALUES (2), (2), (2), (4)
)
SELECT customer_id FROM attendees
INTERSECT
SELECT customer_id FROM downloaders
ORDER BY customer_id;

The result is one row, 2. Both inputs contain that ID, while 1, 3, and 4 occur on only one side. The repeated 2 still appears once because the SQL INTERSECT operator removes duplicate result rows. The final ORDER BY makes display order explicit; without it, do not rely on the order in which rows appear.

Replace the CTEs with your own tables once the example makes sense. Run each SELECT alone first, including its filters. Then combine them and compare the shared values with the output. This makes an SQL query intersection easier to debug because you can see whether a missing value was excluded before the set operation ever ran.

To intersect results in SQL accurately, apply the intended date and status filters to each source before comparing them.

Match columns by position

Each input must return the same number of columns, and corresponding positions must have compatible types. The database compares the first selected value on the left with the first on the right, the second with the second, and so on. Names do not establish the pairing. PostgreSQL calls this requirement union compatibility.

For example, selecting (customer_id, region) on both sides asks for an entire pair to appear in both results. A customer in West on one side and East on the other produces no matching pair, even if the ID is identical. Selecting only the ID changes the question to whether that customer appears in both lists.

SQL’s INTERSECT operator therefore answers a question about the chosen combination of values. Add a column only when it belongs in the definition of a match.

When you intersect SQL outputs, check units and meaning as well as data types. An integer customer ID and an integer order ID may be type-compatible but represent different things. If corresponding types differ, use an explicit CAST where it expresses the intended comparison; inspect the converted values before trusting the result. The SQL INTERSECT operator compares the projected rows, not every column in the underlying tables.

Duplicates and NULL values

Plain INTERSECT returns distinct shared rows. PostgreSQL also supports INTERSECT ALL: if a row occurs m times on the left and n times on the right, it appears min(m, n) times in the result, as described in the PostgreSQL SELECT reference. Change the operator in the example to INTERSECT ALL and the result contains two rows with 2. There are two copies among attendees and three among downloaders, so two pairs can be accounted for.

Use the SQL intersection with ALL when repeated occurrences carry meaning, such as matching quantities in two lists. Use the default form when the question is simply which distinct values are shared. For multiple columns, duplication applies to the complete selected row: (2, 'West') and (2, 'East') are different rows.

NULL deserves a separate test. In SQL Server, the set operator rules treat two NULL values as equal when determining distinct rows. Thus, if both inputs contain a single-column NULL, plain INTERSECT can return one NULL. This differs from an ordinary join condition such as a.id = b.id, where NULL values do not match each other. To exclude unknown IDs from an intersection in SQL, add WHERE customer_id IS NOT NULL to both inputs.

When a join is the better tool

Choose the SQL INTERSECT operator for a distinct list of complete rows common to both inputs. Choose INTERSECT ALL where your engine supports it and shared multiplicity matters. Choose a join when you need to bring together columns from both tables, or when the match condition involves a range or another relationship rather than equality of the projected rows.

For example, a join can pair an attendee with every matching download record and return each download date. That changes the number of rows: two attendee records and three matching download records can produce six combinations. SELECT DISTINCT can collapse projected duplicates, but it does not automatically reproduce the NULL behavior or the multiplicity of INTERSECT ALL. Decide which result you want before replacing SQL’s INTERSECT operator with a join.

To test an intersection between SQL queries, include a repeated ID and a NULL in your fixture, then compare the output with the intended rules.

Check the database dialect

PostgreSQL supports both forms; its reference specifies their duplicate behavior. MySQL supports INTERSECT and INTERSECT ALL beginning with version 8.0.31, according to the MySQL set operations manual. SQL Server documents INTERSECT without an ALL option in its syntax. Check the engine and version before relying on the variant that retains duplicates.

To intersect SQL query results across dialects, also check type conversion and collation rules. For instance, SQL Server requires comparable types and applies collation precedence to character comparisons. Differences in case sensitivity can affect whether text values match. Parenthesize mixed set operations when the intended grouping needs to be clear, and place the final sort on the combined result.

If the query fails or returns nothing

The most likely first failure is a mismatch between the two select lists: a different number of columns, incompatible types, or columns in the wrong order. A database error points toward the first two; a valid query with an unexpectedly empty result can reveal the third. Run each side separately, compare the number, position, type, and actual values of its output columns, then align the projections or cast the values deliberately. Rerun the intersection of the SQL results with a small known matching row before restoring the full filters.