SQL: What's the purpose/benefit of joining on multiple conditions?

Question

Why would a query be joined on more than one condition?

For example:

SELECT *
FROM Sales s
    JOIN Customers c ON c.CustomerID = s.CustomerID
                        AND c.TrasactionID = s.TransactionID

The specific fields being joined aren't important in this example. I just made them up to help illustrate what I'm asking. I've seen queries like this. Is this typically used just for performance reasons or are there times when it's necessary to use more than one join condition? Thanks!

Answer 1

... are there times when it's necessary to use more than one join condition?

Yes, absolutely. This can occur often in a relational database to ensure that the information of one table is aligned correctly to information of another. Below is a real example where it is NECESSARY to use multiple column references to form the join:

I have two tables Evalulation and Value

In both tables, there are four columns. But three of the four are the same. In other words, they both have CaseNum, FileNum, ActivityNum. In addition to those columns, Evaluation has column Grade and Value has column Score.

select E.CaseNum, E.FileNum, E.ActivityNum,E.Grade,V.score 
from Evalulation E
inner join Value V ON E.CaseNum=V.CaseNum 
                  AND E.FileNum=V.FileNum
                  AND E.ActivityNum=V.ActivityNum

See: How to Join two tables by multiple columns in SQL?

---

Other noteworthy items here is that a "join condition" can be determined by the fact that 2 different tables are referred to e.g. E.CaseNum=V.CaseNum (something in table E is equal to something in table V).

In contrast a "filtering condition" typically compares a column to a constant e.g. O.orderdate >= '2017-01-01'

However it's not always so easy to differentiate because you might compare values of one table to another like this: O.orderdate < S.shipdate and predicates of this nature could sometimes be be valid join conditions but frequently they are filtering conditions.

"join conditions" should always be used as part of the defined joins but filtering conditions can optionally be used in the WHERE clause BUT you need to be careful with filtering conditions if using any OUTER JOIN types (left/right/full).

The top 2 queries are NOT equivalent even if they look very similar.

-- list every employee, show dependent details if very young child
select *
from employee e
LEFT OUTER JOIN dependents d on e.id = d.empid and d.birthdate > '2017-01-01'

-- list only employees with very young child
select *
from employee e
LEFT OUTER JOIN dependents d on e.id = d.empid 
where d.birthdate > '2017-01-01'

-- query above produces the same result as this one
select *
from employee e
INNER JOIN dependents d on e.id = d.empid 
where d.birthdate > '2017-01-01'

---

Is this [multiple join conditions] typically used just for performance reasons ...?

Hopefully you can now see it isn't "just for performance". Join conditions are quite distinct from filtering conditions and using multiples is determined by necessity. Using "filtering conditions" as part of joins can be optional (e.g. if only dealing with INNER JOINS) but even these can change the join outcome when using OUTER JOINS.

Answer 2

It depends on what would define a record for your resultset. I am taking dummy table structure here. In production, table structures nearly always have some thing to uniquely identify a record.

Suppose, in one table i have records of students (name, section, age, rollno) but in other i have same student name for different subjects in another table. That table has data like (rollno, section, subject, marks, standard, year).

Now, you can fetch different kinds of data from this.

1. Which student got what marks in which subjects in year 2010.

2. A particular student got what marks in this school all across scademic career.

3. What was the average percentage in maths for standard 9, section 2.

4. How many students in particular section passed and failed.

5. Who topped in which standard and section. What is that person's roll no. What is the age.

Depending on what kind of your query is, you may need to match different columns to identify data uniquely in your resultset. A resultset may have multiple rows for same student, standard, section but subject will be different. In another case, you will be happy with rollno and name.

Based on what you need as output, you may need INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN. Sometimes, you may need to create a view or CTE, and join with that. Other times, APPLY can provide resultant dataset. If you need to aggregate data certain ways, PIVOT, UNPIVOT, GROUPING functions can help ROLLUP or CUBE the data. At other times, creating a function to match in the where clause or a subquery can provide the resultset. So, the way you are going to query the db to get the resultset you want depends on the table structures you have created for data organization. There is too much of stuff in SQL for helping in getting the resultset, and this may not be the best place for a tutorial.

If you really want to learn quality stuff about working and querying with SQL, search for Itzik ben-gan and his books on sql server. Or you can get Microsoft 70-461 training book.

Answer 3

As far as i understand your question

basically joins are used to get relevant data from multiple tables which are connected internally to each other.

there were also many other options for getting data using joins are like (Left join,right join,inner join, outer join etc) each join type having its own pattern of getting data.

AND conditions like (where,and,after on keyword comparison) are basically used to filter out exact result as user wants.

because when ever we use join, there are possibility to get duplicate records. to remove them we use multiple conditions.

Answer 4

In simple : Both of them are equivalent in results and performance!

The query optimizer is smart enough to decide. The presence of the filter condition in JOIN or in WHERE clause would have no effect.

The execution will remain the same and neither performance would deteriorate nor increase.

So both queries in your example would run the same way.

But keep this in mind when it comes to OUTER JOINs, it is slightly different. The core logic of the query can change If you apply the filter in WHERE clause OR JOIN clause, the join can no longer remain an outer join and could become an inner join in case of LEFT/RIGHT outer join OR become LEFT/RIGHT outer join in case of FULL OUTER JOIN.

I hope to be helpful for you:)

Answer 5

basically, if you have 2 lines for customer_id=A on each table with transaction T1 and T2, with only join on customer, it means that you will de-multiply your number of line. Each A line of sales table will be joined to the two A lines of the customers table. The output for customer_id=A will be 4 lines.

If you add the condition on transaction_id, the lines with customer_id = A and transaction_id = T1 in sales will only be join to the correspond line in customers. In that case, the output for customer_id=A will be only 2 lines.

In addition to this, you can have also performance reason if the 2 columns are used in index. If you only call one of them, the index may be skipped resulting in slow performances.