Difference Between "On .. And" and "On .. Where" in SQL Left Join

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

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.

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

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.

Difference between Where and Join on Id

This is very good practice -- in fact, you cannot (easily) get this logic in a WHERE clause.

A LEFT JOIN returns all rows in the first table -- even when there are no matches in the second.

So, this returns all rows in the preceding tables -- and any rows from Client where ClientId = 1. If there is no match on that ClientId, then the columns will be NULL, but the rows are not filtered.

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
      )

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');

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.

what is the difference between left join and left outer join?

The OUTER keyword is optional across most popular SQL distributions, which means there is absolutely no difference between a LEFT JOIN and a LEFT OUTER JOIN

---

Related Topics