Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference between "and" and "where" in joins

Tags:

sql

join

Whats the difference between

SELECT DISTINCT field1 
  FROM table1 cd  
  JOIN table2 
    ON     cd.Company = table2.Name 
       and table2.Id IN (2728) 

and

SELECT DISTINCT field1 
  FROM table1 cd  
  JOIN table2 
    ON cd.Company = table2.Name 
 where table2.Id IN (2728) 

both return the same result and both have the same explain output

like image 942
Midhat Avatar asked Apr 01 '10 08:04

Midhat


People also ask

What is difference between and and WHERE in joins?

So in your first example you are saying that the tables are related by cd. Company = table2.Name AND table2.Id IN (2728) . When you use the WHERE clause, you are saying that the relationship is defined by cd. Company = table2.Name and that you only want the rows where the condition table2.Id IN (2728) applies.

What is the difference between on and WHERE in joins in SQL?

Is there a difference between the WHERE and ON clause? Yes. ON should be used to define the join condition and WHERE should be used to filter the data.

Can we use WHERE clause with joins?

To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.


1 Answers

Firstly there is a semantic difference. When you have a join, you are saying that the relationship between the two tables is defined by that condition. So in your first example you are saying that the tables are related by cd.Company = table2.Name AND table2.Id IN (2728). When you use the WHERE clause, you are saying that the relationship is defined by cd.Company = table2.Name and that you only want the rows where the condition table2.Id IN (2728) applies. Even though these give the same answer, it means very different things to a programmer reading your code.

In this case, the WHERE clause is almost certainly what you mean so you should use it.

Secondly there is actually difference in the result in the case that you use a LEFT JOIN instead of an INNER JOIN. If you include the second condition as part of the join, you will still get a result row if the condition fails - you will get values from the left table and nulls for the right table. If you include the condition as part of the WHERE clause and that condition fails, you won't get the row at all.

Here is an example to demonstrate this.

Query 1 (WHERE):

SELECT DISTINCT field1
  FROM table1 cd
  LEFT JOIN table2
    ON cd.Company = table2.Name
 WHERE table2.Id IN (2728);

Result:

field1
200

Query 2 (AND):

SELECT DISTINCT field1
  FROM table1 cd
  LEFT JOIN table2
    ON cd.Company = table2.Name
   AND table2.Id IN (2728);

Result:

field1
100
200

Test data used:

CREATE TABLE table1 (Company NVARCHAR(100) NOT NULL, Field1 INT NOT NULL);
INSERT INTO table1 (Company, Field1) VALUES
('FooSoft', 100),
('BarSoft', 200);

CREATE TABLE table2 (Id INT NOT NULL, Name NVARCHAR(100) NOT NULL);
INSERT INTO table2 (Id, Name) VALUES
(2727, 'FooSoft'),
(2728, 'BarSoft');
like image 84
Mark Byers Avatar answered Sep 30 '22 17:09

Mark Byers