CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-database
Chaitanya K
Chaitanya K

Reputation: 71

Multiple conditions in SQL joins

How to specify multiple conditions in SQL joins? I know A.key = B.key is mandatory apart from this, is the below correct for specifying multiple conditions in SQL?

ON (A.key=B.key and/or cond1 and/or cond2 ...etc)

OR

ON (A.key=B.key where cond1 and/or cond2 ..etc)

Upvotes: 4

Views: 16060

Answers (4)

Arun Pati
Arun Pati

Reputation: 125

I think your first option is correct.

You can use multiple join conditions like:

ON (A.key=B.key and/or cond1 and/or cond2 ...etc).

For the second one, WHERE should come after the ON clause(all the joins using JOIN clause). Then, this will filter the data after the JOIN. Just close the bracket before WHERE, then it will work as well. Like:

ON (A.key=B.key) WHERE cond1 and/or cond2 ..etc

Both above options can be used, depending upon your requirement.

Upvotes: 1

I A Khan
I A Khan

Reputation: 8839

SELECT A.Column1, B.Column2 FROM Table1 A LEFT OUTER JOIN Table2 B ON A.KEY=B.KEY 
AND Condition 1 AND ( Condition 2 OR Condition 3) OR Condition 4

Upvotes: 0

Veera
Veera

Reputation: 3492

Just Specify the conditions after the ON condition.

SELECT A.ColumnName, B.ColumnName FROM Table1 A <LEFT / RIGHT / INNER JOIN> Table2 B ON
A.Key = B.Key 
AND A.Key > 0 
AND (B.ColumnName <= 10 OR B.ColumnName >= 20)
AND A.Key > B.Key

Upvotes: 1

Andrei Hirsu
Andrei Hirsu

Reputation: 696

You can specify as many conditions as you like in the join like:

ON A.Key=B.Key
AND b.value>100
AND (a.value>b.value OR b.value2 IS NULL)

Upvotes: 10

Related Questions