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

namrata.dhanawade-1143388

Ten Centuries

Points: 1269

December 9, 2009 at 3:53 am

#1089729

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

1000 40 1000 30

1001 30 1000 50

1000 50 1000 60

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 -

RowID TableID RowID

1000 30 1000

1001 30 1001

1002 NULL NULL

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

namrata.dhanawade-1143388

Ten Centuries

Points: 1269

December 9, 2009 at 8:38 am

#1089912

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

namrata.dhanawade-1143388

Ten Centuries

Points: 1269

December 9, 2009 at 8:48 am

#1089931

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

namrata.dhanawade-1143388

Ten Centuries

Points: 1269

December 9, 2009 at 8:59 am

#1089939

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

namrata.dhanawade-1143388

Ten Centuries

Points: 1269

December 9, 2009 at 9:35 am

#1089979

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