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 is the difference between INNER JOIN and OUTER JOIN ?
Assuming you're joining on columns with no duplicates, which is a very common case:
An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
Examples
Suppose you have two tables, with a single column each, and data as follows:
A B
- -
1 3
2 4
3 5
4 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Right outer join
A right outer join will give all rows in B, plus any common rows in A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a(+) = b.b;
a | b
-----+----
3 | 3
4 | 4
null | 5
null | 6
Full outer join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
What is difference between INNER join and OUTER join
Inner join - An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
Left outer join -
A left outer join will give all rows in A, plus any common rows in B.
Full outer join -
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
check this
SQL Join Differences
Check this article:
A Visual Explanation of SQL Joins
Inner Join:
Left Outer Join:
Right Outer Join:
Is there a fundamental difference between INTERSECT and INNER JOIN?
They are very different, even in your case.
The INNER JOIN
will return duplicates, if id
is duplicated in either table. INTERSECT
removes duplicates. The INNER JOIN
will never return NULL
, but INTERSECT
will return NULL
.
The two are very different; one is an operator that generally matches on a limited set of columns and can return zero rows or more rows in either table. The other is a set-based operator that compares complete rows between two sets and can never return more rows than in the smaller table.
SQL JOIN: is there a difference between USING, ON or WHERE?
There is no difference in performance.
However, the first style is ANSI-89 and will get your legs broken in some shops. Including mine. The second style is ANSI-92 and is much clearer.
Examples:
Which is the JOIN, which is the filter?
FROM T1,T2,T3....
WHERE T1.ID = T2.ID AND
T1.foo = 'bar' AND T2.fish = 42 AND
T1.ID = T3.ID
FROM T1
INNER JOIN T2 ON T1.ID = T2.ID
INNER JOIN T3 ON T1.ID = T3.ID
WHERE
T1.foo = 'bar' AND T2.fish = 42
If you have OUTER JOINs (=*
, *=
) then the 2nd style will work as advertised. The first most likely won't and is also deprecated in SQL Server 2005+
The ANSI-92 style is harder to bollix too. With the older style you can easily end up with a Cartesian product (cross join) if you miss a condition. You'll get a syntax error with ANSI-92.
Edit: Some more clarification
- The reason for not using "join the where" (implicit) is the dodgy results with outer joins.
- If you use explicit OUTER JOINs + implicit INNER JOINs you'll still get dodgy results + you have inconsistency in usage
It isn't just syntax: it's about having a semantically correct query
Edit, Dec 2011
SQL Server logical query processing order is FROM, ON, JOIN, WHERE...
So if you mix "implicit WHERE inner joins" and "explicit FROM outer joins" you most likely won't get expected results because the query is ambiguous...
Difference between SQL JOIN and querying from two tables
There is a small difference in syntax, but both queries are doing a join on the P_Id
fields of the respective tables.
In your second example, this is an implicit join, which you are constraining in your WHERE
clause to the P_Id
fields of both tables.
The join is explicit in your first example and the join clause contains the constraint instead of in an additional WHERE
clause.
Related Topics
How to Query a Value in SQL Server Xml Column
Custom Serial/Autoincrement Per Group of Values
SQL Query to Return Differences Between Two Tables
How to Order by With Union in Sql
Get the Last Inserted Row Id (With SQL Statement)
How to Get Column Names from a Table in Oracle
Default Row Order in Select Query - SQL Server 2008 VS SQL 2012
How to Include "Zero"/"0" Results in Count Aggregate
SQL Query to Insert Datetime in SQL Server
Common Table Expression, Why Semicolon
Generating a Random & Unique 8 Character String Using MySQL
Is There a Shortcut For Select * From
How to Dump the Data of Some Sqlite3 Tables
Passing an Array of Parameters to a Stored Procedure
Calculating Difference Between Two Timestamps in Oracle in Milliseconds