Strange Join Type

[dhulbert]

I'm looking at a script writen bu someone who isn't around anymore and I have a found something in it I haven't come across before.

In the sample script below the subselect c is joined in a way I've never used.

I'm looking for some advice/opinion as to what sort of join it defaults to when it's done like this and why would you do it like this rather than a more "traditional" inner/left/right join type?

SELECT	*
FROM	TABLEA A
	INNER JOIN
	TABLEB B 
	ON
	A.FIELD1 = B.FIELD1,
	
	(SELECT FIELD3, MAX(FI4LD4) FROM TABLE C) C
		
WHERE	B.FIELD3 = C.FIELD3

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Rhys666]

I thinks thats on old non-ansi style join and is probably equivalent to;

SELECT
	*
FROM
	TABLEA A
INNER JOIN
	TABLEB B ON A.FIELD1 = B.FIELD1
INNER JOIN
(
	SELECT 
		FIELD3
		,MAX(FI4LD4)
	FROM
		TABLE C
) C ON B.FIELD3 = C.FIELD3

...or...

SELECT
	*
FROM
	TABLEA A
	,TABLEB B
	,(
		SELECT 
			FIELD3
			,MAX(FI4LD4)
		FROM 
			TABLE C
	)
WHERE
	A.FIELD1 = B.FIELD1
AND
	B.FIELD3 = C.FIELD3

It is particularly bad form to mix joing styles in that way though

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[Qik3Coder]

It's a standard inline select, with the weird caveat that it's in a join instead of the projection list (select part)

It's the lazy mans way of saying I have all the rest working, but I need this one other field. It prevents returning additional rows, which might skew some other count, but it's just lazy (and inefficient). It runs that select for each row, instead joining and thinning like it should.

You have to be careful when correcting those, because it's like taking a matchbook out from under a table leg. It's there for a reason. You will want to watch any results that do COUNT or SUM.

Lodlaiden




You've got questions and source code. We want both!

 

[markros]

The above is a mixture of the INNER JOIN and old style WHERE JOIN. I suggest to re-write this way

SELECT
    *
FROM
    TABLEA A
INNER JOIN
    TABLEB B ON A.FIELD1 = B.FIELD1
INNER JOIN
(
    SELECT 
        FIELD3
        ,MAX(FI4LD4)
    FROM
        TABLE C
) C ON B.FIELD3 = C.FIELD3

PluralSight Learning Library