If we do not know exactly which column to join to, can we use an or clause?

Question

Post reply

If we do not know exactly which column to join to, can we use an or clause?

namrata.dhanawade-1143388

Ten Centuries

Points: 1269
More actions
December 9, 2009 at 3:42 am

#136752

Hi
I have 2 tables -
Table1 with columns RowID and TableID
Table2 with columns RowID1, TableID1, RowID2, TableID2
Now I need to join these two tables. Only thing is, I am not sure whether to Join RowID from Table1
to RowID1 or RowID2
I am assuming I CANT do the following :
Select RowID
from Table1 a
inner join Table2 b on (a.RowID = b.RowID1 OR a.RowID = b.RowID2) and (a.TableID = b.TableID1
OR a.TableID = b.TableID2)
If not, how can I go about it?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 1

I guess you can use the OR. I just tried it on temp tables. 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Left join twice to the same table:

SELECT a.*,

ISNULL(b1.TableID1, b2.TableID2) AS TableID,

ISNULL(b1.RowID1, b2.RowID2) AS RowID

FROM Table1 a

LEFT JOIN Table2 b1 ON b1.TableID1 = a.TableID AND b1.RowID1 = a.RowID

LEFT JOIN Table2 b2 ON b2.TableID2 = a.TableID AND b2.RowID2 = a.RowID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 3

This would return the the details of the first Table with NULLS in the columns of the second table, if it does not match either rowID or tableID of the second Table.

Table1

1000 30

1001 30

1002 30

Table 2

100040100030

100130100050

100050100060

Now I execute -

SELECT a.*,

ISNULL(b1.TableID1, b2.TableID2) AS TableID,

ISNULL(b1.RowID1, b2.RowID2) AS RowID

FROM Table1 a

LEFT JOIN Table2 b1 ON b1.TableID1 = 30 AND b1.RowID1 = a.RowID

LEFT JOIN Table2 b2 ON b2.TableID2 = 30 AND b2.RowID2 = a.RowID

It will give the result -

RowIDTableIDRowID

1000301000

1001301001

1002NULLNULL

I however, do not want the last row with NULL values.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Other than those pesky NULLS in the last row of your output where table B has no matches to table A, does the query do what you want it to do?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 5

Well, it does.

I only wanted to retrieve those RowID's from Table1 which would have a match in Table2, irrespective of what columns it comes from.

And I dont want to check for NULLS in the where clause

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

namrata.dhanawade-1143388 (12/9/2009)
And I dont want to check for NULLS in the where clause

Neither do I, it's so untidy.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 7

Well, the OR does work. So I am going to use this -

select RowID

from dbo.Table1 a

inner join dbo.Table2 b on (a.RowID = b.RowID1 and b.TableID1 = 30) OR (a.RowID = b.RowID2 and b.TableID2 = 30)

This works. 🙂

Thanks for your help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

namrata.dhanawade-1143388 (12/9/2009)
Well, the OR does work. So I am going to use this -
select RowID
from dbo.Table1 a
inner join dbo.Table2 b on (a.RowID = b.RowID1 and b.TableID1 = 30) OR (a.RowID = b.RowID2 and b.TableID2 = 30)
This works. 🙂
Thanks for your help.

You're welcome.

A word of warning - there's a BIG problem with using OR in a JOIN like this. You will lose marks because of it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 9

Well then, the alternative would be to use the Condition checks on NULL in the where clause.

Is that alright?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Of course, it's fine!

WHERE (Row1 IS NOT NULL OR Row2 IS NOT NULL) .. or whatever.

But don't you want to know what the problem is, with an OR in a JOIN?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 11

namrata.dhanawade-1143388

Ten Centuries

Points: 1269

December 9, 2009 at 9:12 am

#1089959

Yes, if you could please.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

It scales very poorly. It works fine, just don't try it on large tables.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 13

But I had heard Left joins are slow too. Plus with NULL checks I thought it would be even slower.

Are these OR's even worse?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

You're not very familiar with LEFT JOIN's then?

Nothing beats a little testing - try them for yourself.

All of the different conditions which have come up in this thread.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden