Difference Between Full Join & Inner Join

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

Difference between JOIN and INNER JOIN

They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.

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

What is the difference between JOIN and UNION?

UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it -- completely different operations. Trivial example of UNION:

mysql> SELECT 23 AS bah
    -> UNION
    -> SELECT 45 AS bah;
+-----+
| bah |
+-----+
|  23 | 
|  45 | 
+-----+
2 rows in set (0.00 sec)

similary trivial example of JOIN:

mysql> SELECT * FROM 
    -> (SELECT 23 AS bah) AS foo 
    -> JOIN 
    -> (SELECT 45 AS bah) AS bar
    -> ON (33=33);
+-----+-----+
| foo | bar |
+-----+-----+
|  23 |  45 | 
+-----+-----+
1 row in set (0.01 sec)

SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?

A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything.

A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query's where clause, and in cases where the on condition can't be satisfied for those rows it puts null values in for the unpopulated fields.

This wikipedia article explains the various types of joins with examples of output given a sample set of tables.

CROSS JOIN vs INNER JOIN in SQL

Cross join does not combine the rows, if you have 100 rows in each table with 1 to 1 match, you get 10.000 results, Innerjoin will only return 100 rows in the same situation.

These 2 examples will return the same result:

Cross join

select * from table1 cross join table2 where table1.id = table2.fk_id

Inner join

select * from table1 join table2 on table1.id = table2.fk_id

Use the last method

---

Related Topics