Left Cross Join
-
Is there a kind of join that works like a cross join but will still give me the left table if the right table is empty? Something like a combination of a CROSS JOIN and a LEFT OUTER JOIN.
-
We need the ddl, sample data and required output on this one!!
-
CREATE TABLE A (x bigint)
CREATE TABLE B (y bigint)
INSERT A SELECT 1 UNION SELECT 2 UNION SELECT 3
(Leave Table B empty)
Desired output:
x y
1 NULL
2 NULL
3 NULL
INSERT B SELECT 5 UNION SELECT 6
Desired output:
x y
1 5
2 5
3 5
1 6
2 6
3 6
DROP TABLE A
DROP TABLE B
I want it to be like a cross join, but when Table B is empty, a cross join returns no rows and I want rows.
-
I don't think it's posible in a single statement but here's an idea :
CREATE TABLE A (x bigint)
CREATE TABLE B (y bigint)
INSERT A SELECT 1 UNION SELECT 2 UNION SELECT 3
IF NOT EXISTS (SELECT * FROM B)
BEGIN
SELECT X, NULL AS Y FROM A
END
ELSE
BEGIN
SELECT X, Y FROM A CROSS JOIN B
END
INSERT B SELECT 5 UNION SELECT 6
IF NOT EXISTS (SELECT * FROM B)
BEGIN
SELECT X, NULL AS Y FROM A
END
ELSE
BEGIN
SELECT X, Y FROM A CROSS JOIN B
END
DROP TABLE A, B
-
or then again you can always do a combination cross join and left join:
SELECT a.x, t.y FROM a LEFT JOIN (SELECT x, y FROM a CROSS JOIN b) t ON a.x = t.x ORDER BY t.y, a.x
-
Thanks! Those both worked. Dave's seems simpler so I'll probably go with that one.
-
Ya I agree for the simplicity, but I just ran a perf comparaison and my if version is faster (one less table scan). So now you have to decide how much performance you need out of this (I always suggest to get the max but it's not my decision now
). -
Here's another solution I just found:
SELECT A.x, B.y
FROM A LEFT JOIN B ON 1 = 1
Is there a downside to putting '1 = 1' in an ON clause?
-
Doesn't appear so.
That seems to be the best solution so far.
I would suggest you document this code as for one have never seen anything remotely like this!
-
I'm not sure what you mean by documenting it. I found the solution here: http://www.tek-tips.com/viewthread.cfm?qid=1306991&page=6
-
No I mean in the code... So that when you (or someone else) comes back 2 years from now and read that code, that you know what is happening. Let's just say that this code is far from intuitive!
Thanx anyways for the link
. -
OK, I'll do that. Thanks for the help!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply