What conditions can we put as part of an ON of a JOIN?

Question

I am confused on when putting parts of the WHERE clause inside the ON of a JOIN would not work and why.
I think I have read somewhere that it is best to put clauses as part of the ON clause.
I tried the following (trivial example): In the following I have noticed that for an inner join it will join on columns on the same name but this causes an error for a left join.
Then I noticed that the ON (Employee.sdetails_id=SalaryDetails.sdetails_id and Employee.status<>5) does not filter anything with status = 5. I thought that it would be equivalent to ON (Employee.sdetails_id=SalaryDetails.sdetails_id) WHERE Employee.status<>5 but it is not.
If I modify the Employee table and make as primary key primary key (id, status) then I get the same issue.
Can someone explain how does the ON work and why in this case the status does not filter anything even if it is part of the primary key?

mysql> select * from Employee JOIN SalaryDetails;
+----+------+--------+--------+-------------+-------------+-----------------------------------------------------+
| id | name | status | salary | sdetails_id | sdetails_id | details                                             |
+----+------+--------+--------+-------------+-------------+-----------------------------------------------------+
|  1 | John |      0 |   1000 |           1 |           1 | Hired with the contract of a perm                   |
|  1 | John |      0 |   1000 |           1 |           2 | Hired with perm contract and salary to be increased |
|  2 | Jim  |      0 |   1200 |           1 |           1 | Hired with the contract of a perm                   |
|  2 | Jim  |      0 |   1200 |           1 |           2 | Hired with perm contract and salary to be increased |
|  3 | Nick |      5 |   1500 |           2 |           1 | Hired with the contract of a perm                   |
|  3 | Nick |      5 |   1500 |           2 |           2 | Hired with perm contract and salary to be increased |
+----+------+--------+--------+-------------+-------------+-----------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select * from Employee LEFT JOIN SalaryDetails;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select * from Employee LEFT JOIN SalaryDetails ON (Employee.sdetails_id=SalaryDetails.sdetails_id);
+----+------+--------+--------+-------------+-------------+-----------------------------------------------------+
| id | name | status | salary | sdetails_id | sdetails_id | details                                             |
+----+------+--------+--------+-------------+-------------+-----------------------------------------------------+
|  1 | John |      0 |   1000 |           1 |           1 | Hired with the contract of a perm                   |
|  2 | Jim  |      0 |   1200 |           1 |           1 | Hired with the contract of a perm                   |
|  3 | Nick |      5 |   1500 |           2 |           2 | Hired with perm contract and salary to be increased |
+----+------+--------+--------+-------------+-------------+-----------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from Employee LEFT JOIN SalaryDetails ON (Employee.sdetails_id=SalaryDetails.sdetails_id and Employee.status<>5);
+----+------+--------+--------+-------------+-------------+-----------------------------------+
| id | name | status | salary | sdetails_id | sdetails_id | details                           |
+----+------+--------+--------+-------------+-------------+-----------------------------------+
|  1 | John |      0 |   1000 |           1 |           1 | Hired with the contract of a perm |
|  2 | Jim  |      0 |   1200 |           1 |           1 | Hired with the contract of a perm |
|  3 | Nick |      5 |   1500 |           2 |        NULL | NULL                              |
+----+------+--------+--------+-------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from Employee LEFT JOIN SalaryDetails ON (Employee.sdetails_id=SalaryDetails.sdetails_id) where  Employee.status<>5;
+----+------+--------+--------+-------------+-------------+-----------------------------------+
| id | name | status | salary | sdetails_id | sdetails_id | details                           |
+----+------+--------+--------+-------------+-------------+-----------------------------------+
|  1 | John |      0 |   1000 |           1 |           1 | Hired with the contract of a perm |
|  2 | Jim  |      0 |   1200 |           1 |           1 | Hired with the contract of a perm |
+----+------+--------+--------+-------------+-------------+-----------------------------------+
2 rows in set (0.00 sec)

mysql> show create table Employee;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                   |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Employee | CREATE TABLE `Employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `salary` decimal(5,0) DEFAULT NULL,
  `sdetails_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table SalaryDetails;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                        |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SalaryDetails | CREATE TABLE `SalaryDetails` (
  `sdetails_id` int(11) NOT NULL DEFAULT '0',
  `details` text,
  PRIMARY KEY (`sdetails_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from Employee;
+----+------+--------+--------+-------------+
| id | name | status | salary | sdetails_id |
+----+------+--------+--------+-------------+
|  1 | John |      0 |   1000 |           1 |
|  2 | Jim  |      0 |   1200 |           1 |
|  3 | Nick |      5 |   1500 |           2 |
+----+------+--------+--------+-------------+
3 rows in set (0.00 sec)

mysql> select * from SalaryDetails;
+-------------+-----------------------------------------------------+
| sdetails_id | details                                             |
+-------------+-----------------------------------------------------+
|           1 | Hired with the contract of a perm                   |
|           2 | Hired with perm contract and salary to be increased |
+-------------+-----------------------------------------------------+
2 rows in set (0.00 sec)

score 2 · Accepted Answer · edited May 23 '17 at 10:28

2

An ON clause specifies the logic that decides when a row from one side of the join can be matched with a row from another side of the join. A WHERE clause specifies filtering logic for the whole result set.

In your example using ON (Employee.sdetails_id=SalaryDetails.sdetails_id and Employee.status<>5) you are selecting all rows from Employee to start, then asking the DB to join only when it finds a matching SalaryDetail entry, where the IDs are the same, and the status is not 5. As you can see in your result set Nick doesn't have the details from the SalaryDetails table as those values are null. There are no results because you have asked the DB not to join if the status is 5. This does not prevent it from returning the "left" part of the join from the Employee table.

If you wanted to only include rows where a join actually happened then you would use an INNER JOIN instead of a LEFT JOIN. I would recommend reading up on the different types of join.

In a lot of RDBMSs you can actually put all the logic in the WHERE clause, but it's (subjectively) clearer if you semantically separate joining logic from row filtering when writing SQL. Your DB is going to do the same amount of work internally.

Personally I would write your query as an INNER JOIN, with an ON for the id's, and a filter on the status field in a WHERE clause.

edited May 23 '17 at 10:28

Community

1
1

answered Nov 09 '14 at 18:51

AndySavage

1,729
1
20
34

I understand that the `A LEFT JOIN B` returns all records in A. **But** I was expecting that during the join process all the conditions of the ON part would be applied. So fetching a row from `Employee` it would see that there is no matching row in `SalaryDetail` but since there is the condition of `status <> 5` it would not be part of the result set of the join. – Jim Nov 09 '14 at 18:56
The statements "return all records in A" and "not be part of the result" are mutually exclusive. It returns all records from A, and joins to B based on the conditions you have specified. If a row from A is unable to be joined it is still returned just without the extra data from B - that's exactly what a `LEFT JOIN` does. – AndySavage Nov 09 '14 at 18:59
You have a point. Let me rephrase. I was expecting that by putting the condition in the `ON` it would still be filtered out exactly as when we put it in the `WHERE`.Why did I expect that?Because I thought that `A JOIN B ON (A.col=B.col)` is exactly the same as `A JOIN B WHERE A.col=B.col` – Jim Nov 09 '14 at 19:04
That is only true for `INNER JOIN`s. For the `LEFT` you have in your example they are not equivalent. Some DBs (Oracle being the obvious) still allow you to put your join logic in the `WHERE` but then there is extra syntax (i.e. `(+)` for specifying the optional side) and it's no where near as clear in my opinion. – AndySavage Nov 09 '14 at 19:14
So for INNER JOINs we can add everything in the WHERE clause as part of the ON? – Jim Nov 09 '14 at 20:50
Yes, though it wouldn't make any sense to do so. Keep `ON` clauses only for joining logic, and `WHERE` clauses for row filtering. – AndySavage Nov 10 '14 at 01:10

score 0 · Answer 2 · answered Nov 09 '14 at 18:46

0

SELECT * FROM T1 LEFT JOIN T2 ON condition

select everything from T1, if something is found matching the condition, then join it

SELECT * FROM T1 INNER JOIN T2 ON condition

select everything from T1 and join T2, but ONLY if a matching part was found in T2

so use INNER JOIN to filter

answered Nov 09 '14 at 18:46

mech

617
6
16

why is my answer not useful? – mech Nov 09 '14 at 18:53

What conditions can we put as part of an ON of a JOIN?

2 Answers2