I have a SQL that looks like this:
SELECT * FROM foo WHERE foo.bar IN (SELECT foobar.bar FROM foobar)
This is not good, right? The nested SELECT will cause things to slow down? How should I query something like this?
asked Oct 31, 2012 at 22:25
StackOverflowNewbie
40.9k114 gold badges293 silver badges454 bronze badges
-
1The nested SQL is not dependent though. You could look at LEFT JOIN and filter NULL records of the right hand columns.Ja͢ck– Ja͢ck2012-10-31 22:26:30 +00:00Commented Oct 31, 2012 at 22:26
Add a comment
|
2 Answers
The IN clause is perfectly valid SQL, but it's not always the preferred way. I've often found that MySQL's performance on them is pretty poor, even if foo.bar is indexed.
If you change it to a join, you have to be careful if the second table has multiple rows that match the join condition for each row of the first table, because the join will produce multiple result rows. If this is possible, the join should be:
SELECT f.*
FROM foo f
INNER JOIN (SELECT DISTINCT bar FROM foobar) fb USING (bar);
The ultimate answer is that you should use EXPLAIN to see how different forms of the query will be executed. But if you're not experiencing a performance problem in the first place, don't sweat it.
If you want all the rows where f.bar does not exist in foobar, NOT IN is usually the most succinct way to write it. But it can also be written using a LEFT OUTER JOIN:
SELECT f.*
FROM foo f
LEFT OUTER JOIN foobar fb USING (bar)
WHERE fb.bar IS NULL
You don't need the subquery in this case because you're only reporting the non-matching rows, so there obviously can't be multiple matches in the result.
2 Comments
Geoff Montee
I'd probably switch it to
SELECT DISTINCT f.* FROM foo f JOIN foobar fb USING(bar) to make it a little easier to read. No nested query that way.
StackOverflowNewbie
And what if I wanted to all records from
foo where foo.bar does not exist in foobar.bar?You can also rewrite this as
Select
*
From
foo f
Where
Exists ( -- If you want the opposite, put Not in front here
Select 'x'
From foobar fb
Where f.bar = fb.bar
);
Over the years, different DBMS's have dealt better with the query in one of the different forms, even though they are all equivalent. Most that I've tried can generate the same plan for both now. Not something I've tried on mysql, though.
