...

View Full Version : What is wrong with sql



xiaodao
04-02-2009, 09:24 AM
Hi

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=1


the result should return records of Series with NULL rating if the rating table does not have the matching series_seriesid

_Aerospace_Eng_
04-02-2009, 09:56 AM
Change these

series.*
to this

*
Do it for the other one you have too.

Old Pedant
04-02-2009, 11:13 PM
No reason to use an inner SELECT here.

Just need to make sure that you code the ON condition correctly!



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


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.

bdl
04-03-2009, 04:09 PM
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:

SELECT series.* FROM series

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 ('*').

That's not the fix to the statement, but you get the idea.


Yes, the SELECT ALL statement should be avoided, absolutely.

Old Pedant
04-03-2009, 11:30 PM
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 *

WHY?

There is NO DIFFERENCE AT ALL between


SELECT series.* FROM series

and


SELECT * FROM series

None. Not one iota.

There *IS* a difference if you were using mutiple tables:


SELECT series.* FROM series LEFT JOIN rating ...

Indeed, that would get ONLY the fields from the series table. Wheras


SELECT * FROM series LEFT JOIN rating ...

would be the same as doing


SELECT series.*, rating.* FROM series LEFT JOIN rating ...

Granted that using * is bad, but there is truly ZERO difference between


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

and the same code using just * in place of series.* and rating.*

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:


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

But of course it's much better to just do the LEFT JOIN as I showed it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum