Interview Question in SQL Server Performance Tuning
Interview Question :: Which of these three queries performs the best
All these three queries find rows from two tables that does not match each other based on a single column comparison. IN Short, Find all records of both tables, except for the matching ones. They produce the same result. But we're looking for the query that uses the least resources and best performance
Answer specific to SQL Server in relation to the best performance based on execution plan will be chosen as the best answer. But general answers with good explanation will also be considered.
query1:
SELECT tbl.a FROM (SELECT table1.a FROM table1 UNION ALL SELECT table2.a FROM table2) as tbl GROUP BY a HAVING COUNT(*)=1
query2:
SELECT Table1.A
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.A =Table2.A
where Table2.A is null
UNION
SELECT TABLE2.A
FROM TABLE2 LEFT OUTER JOIN TABLE1 ON TABLE1.A = TABLE2.A
WHERE TABLE1.A is null
query3:
SELECT table1.a FROM table1
WHERE table1.a NOT IN (SELECT table2.a FROM table2)
UNION
SELECT table2.a FROM table2
WHERE table2.a NOT IN (SELECT table1.a FROM table1)
Loading ...