What's the difference between where clause and on clause when table left join?
The where
clause applies to the whole resultset; the on clause
only applies to the join in question.
In the example supplied, all of the additional conditions related to fields on the inner side of the join - so in this example, the two queries are effectively identical.
However, if you had included a condition on a value in the table in the outer side of the join, it would have made a significant difference.
You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
For example:
select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 and t2.f4=1
select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 where t2.f4=1
- do different things - the former will left join to t2 records where f4 is 1, while the latter has effectively been turned back into an inner join to t2.
SQL LEFT JOIN: difference between WHERE and condition inside AND
with a left join
there is a difference
with condition on left join
rows with column > 10
will be there filled with nulls
with where
condition rows will be filtered out
with a inner join
there is no difference
example:
declare @t table (id int, dummy varchar(20))
declare @a table (id int, age int, col int)
insert into @t
select * from (
values
(1, 'pippo' ),
(2, 'pluto' ),
(3, 'paperino' ),
(4, 'ciccio' ),
(5, 'caio' ),
(5, 'sempronio')
) x (c1,c2)
insert into @a
select * from (
values
(1, 38, 2 ),
(2, 26, 5 ),
(3, 41, 12),
(4, 15, 11),
(5, 39, 7 )
) x (c1,c2,c3)
select t.*, a.age
from @t t
left join @a a on t.ID = a.ID and a.col > 10
Outputs:
id dummy age
1 pippo NULL
2 pluto NULL
3 paperino 41
4 ciccio 15
5 caio NULL
5 sempronio NULL
While
select t.*, a.age
from @t t
left join @a a on t.ID = a.ID
where a.col > 10
Outputs:
id dummy age
3 paperino 41
4 ciccio 15
So with LEFT JOIN
you will get ALWAYS all the rows from 1st table
If the join condition is true, you will get columns from joined table filled with their values, if the condition is false their columns will be NULL
With WHERE
condition you will get only the rows that match the condition.
SQL JOIN - WHERE clause vs. ON clause
They are not the same thing.
Consider these queries:
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345
and
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
AND Orders.ID = 12345
The first will return an order and its lines, if any, for order number 12345
. The second will return all orders, but only order 12345
will have any lines associated with it.
With an INNER JOIN
, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.
Difference between on .. and and on .. where in SQL Left Join?
create table A(id int);
create table B(id int);
INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);
INSERT INTO B VALUES(1);
INSERT INTO B VALUES(2);
INSERT INTO B VALUES(3);
SELECT * FROM A;
SELECT * FROM B;
id
-----------
1
2
3
id
-----------
1
2
3
Filter on the JOIN to prevent rows from being added during the JOIN process.
select a.*,b.*
from A a left join B b
on a.id =b.id and a.id=2;
id id
----------- -----------
1 NULL
2 2
3 NULL
WHERE will filter after the JOIN has occurred.
select a.*,b.*
from A a left join B b
on a.id =b.id
where a.id=2;
id id
----------- -----------
2 2
Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?
The on
clause is used when the join
is looking for matching rows. The where
clause is used to filter rows after all the joining is done.
An example with Disney toons voting for president:
declare @candidates table (name varchar(50));
insert @candidates values
('Obama'),
('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values
('Mickey Mouse', 'Romney'),
('Donald Duck', 'Obama');
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
and v.voter = 'Donald Duck'
This still returns Romney
even though Donald
didn't vote for him. If you move the condition from the on
to the where
clause:
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
where v.voter = 'Donald Duck'
Romney
will no longer be in the result set.
In SQL / MySQL, what is the difference between ON and WHERE in a join statement?
WHERE
is a part of the SELECT
query as a whole, ON
is a part of each individual join.
ON
can only refer to the fields of previously used tables.
When there is no actual match against a record in the left table, LEFT JOIN
returns one record from the right table with all fields set to NULLS
. WHERE
clause then evaluates and filter this.
In your query, only the records from gifts
without match in 'sentgifts' are returned.
Here's the example
gifts
1 Teddy bear
2 Flowers
sentgifts
1 Alice
1 Bob
---
SELECT *
FROM gifts g
LEFT JOIN
sentgifts sg
ON g.giftID = sg.giftID
---
1 Teddy bear 1 Alice
1 Teddy bear 1 Bob
2 Flowers NULL NULL -- no match in sentgifts
---
SELECT *
FROM gifts g
LEFT JOIN
sentgifts sg
ON g.giftID = sg.giftID
WHERE sg.giftID IS NULL
---
2 Flowers NULL NULL -- no match in sentgifts
As you can see, no actual match can leave a NULL
in sentgifts.id
, so only the gifts that had not ever been sent are returned.
Difference between where and and clause in join sql query
Base on the following two test result
select a.id, a.name,a.country from table a left join table b
on a.id = b.id
where a.name is not null
is faster (237 Vs 460). As far as I know, it is a standard.
Difference between and and where in joins
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');
Difference between ON and WHERE clauses in SQL table joins
The main difference is when you are using different joins.
Typically you should see the same result if you were to use inner joins, but once you start using LEFT joins the results will change.
Have a look at the following example
SQL Fiddle DEMO
And have a look at the following article (very explanatory)
EDIT for @ShannonSeverance
Schema and Test data
CREATE TABLE Table1 (
ID INT,
Val VARCHAR(20)
);
INSERT INTO Table1 VALUES (1,'a');
INSERT INTO Table1 VALUES (2,'a');
CREATE TABLE Table2 (
ID INT,
Val VARCHAR(20)
);
INSERT INTO Table2 VALUES (1,'a');
and Tests
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.ID = t2.ID AND t1.Val = t2.Val;
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1,Table2 t2
WHERE t1.ID = t2.ID
AND t1.Val = t2.Val;
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID AND t1.Val = t2.Val;
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID
WHERE t1.Val = t2.Val;
What is the difference between On or Where in LEFT JOIN?
Rewriting the 2 queries without the LEFT JOIN / IS NULL
so it's obvious that they can return different data sets in certain cases:
---Query 1
SELECT COUNT(*)
FROM a
WHERE a.status = 2
AND NOT EXISTS
( SELECT *
FROM b
WHERE a.id = b.id
)
and
---Query 2
SELECT COUNT(*)
FROM a
WHERE NOT ( a.status = 2
AND EXISTS
( SELECT *
FROM b
WHERE a.id = b.id
)
)
--- or:
---Query 2
SELECT COUNT(*)
FROM a
WHERE a.status <> 2
OR NOT EXISTS
( SELECT *
FROM b
WHERE a.id = b.id
)
Related Topics
How to See Active SQL Server Connections
How to Version Your Database Schema
Extract Date (Yyyy/Mm/Dd) from a Timestamp in Postgresql
How Can a Left Outer Join Return More Records Than Exist in the Left Table
How to Have Nhibernate Only Generate the SQL Without Executing It
Find the Number of Columns in a Table
SQL Server Operating System Error 5: "5(Access Is Denied.)"
T-Sql: Selecting Column Based on Max(Other Column)
What Does a Transaction Around a Single Statement Do
Do Database Transactions Prevent Race Conditions
How to Find Column Names for All Tables in All Databases in SQL Server
Should I Design a Table with a Primary Key of Varchar or Int
Generate Delete Statement from Foreign Key Relationships in SQL 2008
Get the Distinct Sum of a Joined Table Column
What Is a Self Join For? (In English)
SQL How to Compare Two Tables for Same Data Content