Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
Thread: What is wrong with sql
04-02-2009, 09:24 AM #1
- Join Date
- Sep 2004
- Thanked 2 Times in 2 Posts
What is wrong with sql
what is wrong with this sql
SELECT series.* FROM series LEFT JOIN (select rating.* from rating WHERE rating.USER_USERID=51) ON series.SERIESID=rating.SERIES_SERIESID WHERE series.SERIESID=1flying dagger
04-02-2009, 09:56 AM #2
- Join Date
- Dec 2004
- In a place far, far away...
- Thanked 1,043 Times in 1,019 Posts
Do it for the other one you have too.Code:*
04-02-2009, 11:13 PM #3
No reason to use an inner SELECT here.
Just need to make sure that you code the ON condition correctly!
And I *DISAGREE* with Aerospace. You should avoid doing SELECT * like the plague! Always only SELECT the actual fields you need. There's a LOT of overhead involved in using SELECT * in all too many circumstances.Code:SELECT explicit, list, of, fields FROM series LEFT JOIN rating ON ( rating.USER_USERID=51 AND series.SERIESID=rating.SERIES_SERIESID ) WHERE series.SERIESID=1
04-03-2009, 04:09 PM #4
- Join Date
- Apr 2007
- Camarillo, CA US
- Thanked 83 Times in 82 Posts
Well, what _Aerospace_Eng_ was getting at, I believe, is the fact that this statement is at least redundant if not problematic in and of itself:
You should simply change the series.* to just *. Same thing with ratings.*. Another problem comes into play when you have two tables with repeated field names using SELECT ALL ('*').Code:SELECT series.* FROM series
That's not the fix to the statement, but you get the idea.
Yes, the SELECT ALL statement should be avoided, absolutely.
04-03-2009, 11:30 PM #5
WHY?Code:Well, what _Aerospace_Eng_ was getting at, I believe, is the fact that this statement is at least redundant if not problematic in and of itself: Code: SELECT series.* FROM series You should simply change the series.* to just *
There is NO DIFFERENCE AT ALL between
andCode:SELECT series.* FROM series
None. Not one iota.Code:SELECT * FROM series
There *IS* a difference if you were using mutiple tables:
Indeed, that would get ONLY the fields from the series table. WherasCode:SELECT series.* FROM series LEFT JOIN rating ...
would be the same as doingCode:SELECT * FROM series LEFT JOIN rating ...
Granted that using * is bad, but there is truly ZERO difference betweenCode:SELECT series.*, rating.* FROM series LEFT JOIN rating ...
and the same code using just * in place of series.* and rating.*Code:SELECT series.* FROM series LEFT JOIN ( select rating.* from rating WHERE rating.USER_USERID=51) ON series.SERIESID=rating.SERIES_SERIESID WHERE series.SERIESID=1
The query is utterly bogus from the get-go because he LEFT JOINs to a sub-SELECT but then his ON clause refers to the ENTIRE rating table!
If he really wanted to use a sub-SELECT, he should have coded:
But of course it's much better to just do the LEFT JOIN as I showed it.Code:SELECT series.* FROM series LEFT JOIN ( select rating.* from rating WHERE rating.USER_USERID=51) AS XXX ON series.SERIESID=XXX.SERIES_SERIESID WHERE series.SERIESID=1