News for hackers. Stuff that matters.

A comment on Speeding up SQL queries by orders of magnitude using UNION. Parent comment

Re:Speeding up SQL queries by orders of magnitude using UNION by croes on Saturday March 20, 2021 @04:28PM (#26525863)
This queries have different results. [2] retrieves only the vegetable_ids which are in both tables, [1] gives all ids which are in prices or weights. If vegetable_id null exists in either table [1] result in an extra row with id null, this doesn't happen for query [2]
[ Parent | on HN ]
Re:Speeding up SQL queries by orders of magnitude using UNION by fifilura on Saturday March 20, 2021 @04:31PM (#26525885)
If I replace JOIN with FULL OUTER JOIN, you'll get what you describe. It was just an quick example, but you are right.

There are also things to say about what happens if either table has duplicate vegetable_id:s. At some point it is assumed that you have processed it in such a way that the table is well formed for the purpose.

[ Parent | on HN ]