Use CTE in the WHERE clause without JOIN-CodePudding

I want to search through a list of values in the WHERE clause. The list is in another table and can be returned via CTE. Example:

References table

ref_number	Other irrelevant columns
abcdEF	row
cdefGH	row

WITH References AS (SELECT ref_number FROM References)
SELECT t.ref,t.* FROM my_tables t
WHERE t.ref IN References

This throws an error, I believe IN wants a properly formatted set from the table.

I want to list the references after the IN clause rather than typing them by hand, or otherwise determining the clause to read that condition. I would like the solution for PostgreSQL and for SQL Server if possible. Thank you very much.

CodePudding user response：

You can't just say "IN CTE", you have to say "IN (select x from CTE)" or simply JOIN the CTE to the rest of the query if appropriate.