Join vs EXISTS
Vaquar Khan edited this page Feb 11, 2023
·
5 revisions
I need to check for the existence of some row in a table, I tend to write always a condition like:
`SELECT a, b, c`
`FROM a_table`
`WHERE EXISTS`
`(SELECT * -- This is what I normally write`
`FROM another_table`
`WHERE another_table.b = a_table.b`
`)`
Some other people write it like:
`SELECT a, b, c`
`FROM a_table`
`WHERE EXISTS`
`(SELECT 1 --- This nice '1' is what I have seen other people use`
`FROM another_table`
`WHERE another_table.b = a_table.b`
`)`
When the condition is NOT EXISTS instead of EXISTS: In some occasions, I might write it with a LEFT JOIN and an extra condition (sometimes called an antijoin):
`SELECT a, b, c`
`FROM a_table`
`LEFT JOIN another_table ON another_table.b = a_table.b`
`WHERE another_table.primary_key IS NULL`
======================================================================================================
`SELECT column1, column2`
`FROM table1`
`WHERE EXISTS (`
`SELECT 1`
`FROM table2`
`WHERE table1.column3 = table2.column3`
`)`
`SELECT table1.column1, table1.column2`
`FROM table1`
`JOIN table2`
`ON table1.column3 = table2.column3`