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

A inner join only shows rows if there is a matching record on the other (right) side of the join.

A (left) outer join shows rows for each record on the left hand side, even if there are no matching rows on the other (right) side of the join. If there is no matching row, the columns for the other (right) side would show NULLs.

Inner joins require that a record with a related ID exist in the joined table.

Outer joins will return records for the left side even if nothing exists for the right side.

For instance, you have an Orders and an OrderDetails table. They are related by an "OrderID".

Orders

• OrderID
• CustomerName

OrderDetails

• OrderDetailID
• OrderID
• ProductName
• Qty
• Price

The request

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
 INNER JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

will only return Orders that also have something in the OrderDetails table.

If you change it to OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
  LEFT JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

then it will return records from the Orders table even if they have no OrderDetails records.

You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like WHERE OrderDetails.OrderID IS NULL.

I don't see much details about performance and optimizer in the other answers.

Sometimes it is good to know that only INNER JOIN is associative which means the optimizer has the most option to play with it. It can reorder the join order to make it faster keeping the same result. The optimizer can use the most join modes.

Generally it is a good practice to try to use INNER JOIN instead of the different kind of joins. (Of course if it is possible considering the expected result set.)

There are a couple of good examples and explanation here about this strange associative behavior:

• Are left outer joins associative?
• Does the join order matter in SQL?

The General Idea

Please see the answer by Martin Smith for a better illustations and explanations of the different joins, including and especially differences between FULL OUTER JOIN, RIGHT OUTER JOIN and LEFT OUTER JOIN.

These two table form a basis for the representation of the JOINs below:

CROSS JOIN

SELECT *
  FROM citizen
 CROSS JOIN postalcode

The result will be the Cartesian products of all combinations. No JOIN condition required:

INNER JOIN

INNER JOIN is the same as simply: JOIN

SELECT *
  FROM citizen    c
  JOIN postalcode p ON c.postal = p.postal

The result will be combinations that satisfies the required JOIN condition:

LEFT OUTER JOIN

LEFT OUTER JOIN is the same as LEFT JOIN

SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON c.postal = p.postal

The result will be everything from citizen even if there are no matches in postalcode. Again a JOIN condition is required:

Data for playing

All examples have been run on an Oracle 18c. They're available at dbfiddle.uk which is also where screenshots of tables came from.

CREATE TABLE citizen (id      NUMBER,
                      name    VARCHAR2(20),
                      postal  NUMBER,  -- <-- could do with a redesign to postalcode.id instead.
                      leader  NUMBER);

CREATE TABLE postalcode (id      NUMBER,
                         postal  NUMBER,
                         city    VARCHAR2(20),
                         area    VARCHAR2(20));

INSERT INTO citizen (id, name, postal, leader)
              SELECT 1, 'Smith', 2200,  null FROM DUAL
        UNION SELECT 2, 'Green', 31006, 1    FROM DUAL
        UNION SELECT 3, 'Jensen', 623,  1    FROM DUAL;

INSERT INTO postalcode (id, postal, city, area)
                 SELECT 1, 2200,     'BigCity',         'Geancy'  FROM DUAL
           UNION SELECT 2, 31006,    'SmallTown',       'Snizkim' FROM DUAL
           UNION SELECT 3, 31006,    'Settlement',      'Moon'    FROM DUAL  -- <-- Uuh-uhh.
           UNION SELECT 4, 78567390, 'LookoutTowerX89', 'Space'   FROM DUAL;

Blurry boundaries when playing with JOIN and WHERE

CROSS JOIN

CROSS JOIN resulting in rows as The General Idea/INNER JOIN:

SELECT *
  FROM citizen          c
  CROSS JOIN postalcode p
 WHERE c.postal = p.postal -- < -- The WHERE condition is limiting the resulting rows

Using CROSS JOIN to get the result of a LEFT OUTER JOIN requires tricks like adding in a NULL row. It's omitted.

INNER JOIN

INNER JOIN becomes a cartesian products. It's the same as The General Idea/CROSS JOIN:

SELECT *
  FROM citizen    c
  JOIN postalcode p ON 1 = 1  -- < -- The ON condition makes it a CROSS JOIN

This is where the inner join can really be seen as the cross join with results not matching the condition removed. Here none of the resulting rows are removed.

Using INNER JOIN to get the result of a LEFT OUTER JOIN also requires tricks. It's omitted.

LEFT OUTER JOIN

LEFT JOIN results in rows as The General Idea/CROSS JOIN:

SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON 1 = 1 -- < -- The ON condition makes it a CROSS JOIN

LEFT JOIN results in rows as The General Idea/INNER JOIN:

SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON c.postal = p.postal
 WHERE p.postal IS NOT NULL -- < -- removed the row where there's no mathcing result from postalcode

The troubles with the Venn diagram

An image internet search on "sql join cross inner outer" will show a multitude of Venn diagrams. I used to have a printed copy of one on my desk. But there are issues with the representation.

Venn diagram are excellent for set theory, where an element can be in one or both sets. But for databases, an element in one "set" seem, to me, to be a row in a table, and therefore not also present in any other tables. There is no such thing as one row present in multiple tables. A row is unique to the table.

Self joins are a corner case where each element is in fact the same in both sets. But it's still not free of any of the issues below.

The set A represents the set on the left (the citizen table) and the set B is the set on the right (the postalcode table) in below discussion.

CROSS JOIN

Every element in both sets are matched with every element in the other set, meaning we need A amount of every B elements and B amount of every A elements to properly represent this Cartesian product. Set theory isn't made for multiple identical elements in a set, so I find Venn diagrams to properly represent it impractical/impossible. It doesn't seem that UNION fits at all.

The rows are distinct. The UNION is 7 rows in total. But they're incompatible for a common SQL results set. And this is not how a CROSS JOIN works at all:

Trying to represent it like this:

..but now it just looks like an INTERSECTION, which it's certainly not. Furthermore there's no element in the INTERSECTION that is actually in any of the two distinct sets. However, it looks very much like the searchable results similar to this:

For reference one searchable result for CROSS JOINs can be seen at Tutorialgateway. The INTERSECTION, just like this one, is empty.

INNER JOIN

The value of an element depends on the JOIN condition. It's possible to represent this under the condition that every row becomes unique to that condition. Meaning id=x is only true for one row. Once a row in table A (citizen) matches multiple rows in table B (postalcode) under the JOIN condition, the result has the same problems as the CROSS JOIN: The row needs to be represented multiple times, and the set theory isn't really made for that. Under the condition of uniqueness, the diagram could work though, but keep in mind that the JOIN condition determines the placement of an element in the diagram. Looking only at the values of the JOIN condition with the rest of the row just along for the ride:

This representation falls completely apart when using an INNER JOIN with a ON 1 = 1 condition making it into a CROSS JOIN.

With a self-JOIN, the rows are in fact idential elements in both tables, but representing the tables as both A and B isn't very suitable. For example a common self-JOIN condition that makes an element in A to be matching a different element in B is ON A.parent = B.child, making the match from A to B on seperate elements. From the examples that would be a SQL like this:

SELECT *
  FROM citizen c1
  JOIN citizen c2 ON c1.id = c2.leader

Meaning Smith is the leader of both Green and Jensen.

OUTER JOIN

Again the troubles begin when one row has multiple matches to rows in the other table. This is further complicated because the OUTER JOIN can be though of as to match the empty set. But in set theory the union of any set C and an empty set, is always just C. The empty set adds nothing. The representation of this LEFT OUTER JOIN is usually just showing all of A to illustrate that rows in A are selected regardless of whether there is a match or not from B. The "matching elements" however has the same problems as the illustration above. They depend on the condition. And the empty set seems to have wandered over to A:

WHERE clause - making sense

Finding all rows from a CROSS JOIN with Smith and postalcode on the Moon:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
   AND p.area = 'Moon';

Now the Venn diagram isn't used to reflect the JOIN. It's used only for the WHERE clause:

..and that makes sense.

When INTERSECT and UNION makes sense

INTERSECT

As explained an INNER JOIN is not really an INTERSECT. However INTERSECTs can be used on results of seperate queries. Here a Venn diagram makes sense, because the elements from the seperate queries are in fact rows that either belonging to just one of the results or both. Intersect will obviously only return results where the row is present in both queries. This SQL will result in the same row as the one above WHERE, and the Venn diagram will also be the same:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
INTERSECT
SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE p.area = 'Moon';

UNION

An OUTER JOIN is not a UNION. However UNION work under the same conditions as INTERSECT, resulting in a return of all results combining both SELECTs:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
UNION
SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE p.area = 'Moon';

which is equivalent to:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
   OR p.area = 'Moon';

..and gives the result:

Also here a Venn diagram makes sense:

When it doesn't apply

An important note is that these only work when the structure of the results from the two SELECT's are the same, enabling a comparison or union. The results of these two will not enable that:

SELECT *
  FROM citizen
 WHERE name = 'Smith'

SELECT *
  FROM postalcode
 WHERE area = 'Moon';

..trying to combine the results with UNION gives a

ORA-01790: expression must have same datatype as corresponding expression

For further interest read Say NO to Venn Diagrams When Explaining JOINs and sql joins as venn diagram. Both also cover EXCEPT.

In Simple Terms,

1.INNER JOIN OR EQUI JOIN : Returns the resultset that matches only the condition in both the tables.

2.OUTER JOIN : Returns the resultset of all the values from both the tables even if there is condition match or not.

3.LEFT JOIN : Returns the resultset of all the values from left table and only rows that match the condition in right table.

4.RIGHT JOIN : Returns the resultset of all the values from right table and only rows that match the condition in left table.

5.FULL JOIN : Full Join and Full outer Join are same.

Joins are more easily explained with an example:

To simulate persons and emails stored in separate tables,

Table A and Table B are joined by Table_A.id = Table_B.name_id

Inner Join

Only matched ids' rows are shown.

Outer Joins

Matched ids and not matched rows for Table A are shown.

Matched ids and not matched rows for Table B are shown.

Matched ids and not matched rows from both Tables are shown.

Note: Full outer join is not available on MySQL

Consider below 2 tables:

EMP

empid   name    dept_id salary
1       Rob     1       100
2       Mark    1       300
3       John    2       100
4       Mary    2       300
5       Bill    3       700
6       Jose    6       400

Department

deptid  name
1       IT
2       Accounts
3       Security
4       HR
5       R&D

Inner Join:

Mostly written as just JOIN in sql queries. It returns only the matching records between the tables.

Find out all employees and their department names:

Select a.empid, a.name, b.name as dept_name
FROM emp a
JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security

As you see above, Jose is not printed from EMP in the output as it's dept_id 6 does not find a match in the Department table. Similarly, HR and R&D rows are not printed from Department table as they didn't find a match in the Emp table.

So, INNER JOIN or just JOIN, returns only matching rows.

LEFT JOIN :

This returns all records from the LEFT table and only matching records from the RIGHT table.

Select a.empid, a.name, b.name as dept_name
FROM emp a
LEFT JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security
6       Jose    

So, if you observe the above output, all records from the LEFT table(Emp) are printed with just matching records from RIGHT table.

HR and R&D rows are not printed from Department table as they didn't find a match in the Emp table on dept_id.

So, LEFT JOIN returns ALL rows from Left table and only matching rows from RIGHT table.

Can also check DEMO here.

There are a lot of good answers here with very accurate relational algebra examples. Here is a very simplified answer that might be helpful for amateur or novice coders with SQL coding dilemmas.

Basically, more often than not, JOIN queries boil down to two cases:

For a SELECT of a subset of A data:

• use INNER JOIN when the related B data you are looking for MUST exist per database design;
• use LEFT JOIN when the related B data you are looking for MIGHT or MIGHT NOT exist per database design.

The difference between inner join and outer join is as follow:

1. Inner join is a join that combined tables based on matching tuples, whereas outer join is a join that combined table based on both matched and unmatched tuple.
2. Inner join merges matched row from two table in where unmatched row are omitted, whereas outer join merges rows from two tables and unmatched rows fill with null value.
3. Inner join is like an intersection operation, whereas outer join is like an union operation.
4. Inner join is two types, whereas outer join are three types.
5. outer join is faster than inner join.

A Demonstration

Setup

Hop into psql and create a tiny database of cats and humans. You can just copy-paste this whole section.

CREATE DATABASE catdb;
\c catdb;
\pset null '[NULL]' -- how to display null values

CREATE TABLE humans (
  name text primary key
);
CREATE TABLE cats (
  human_name text references humans(name),
  name text
);

INSERT INTO humans (name)
VALUES ('Abe'), ('Ann'), ('Ben'), ('Jen');

INSERT INTO cats (human_name, name)
VALUES
('Abe', 'Axel'),
(NULL, 'Bitty'),
('Jen', 'Jellybean'),
('Jen', 'Juniper');

Querying

Here's a query we'll run several times, changing [SOMETHING JOIN] to the various types to see the results.

SELECT
humans.name AS human_name,
cats.name AS cat_name
FROM humans
[SOMETHING JOIN] cats ON humans.name = cats.human_name
ORDER BY humans.name;

An INNER JOIN returns all human-cat pairs. Any human without a cat or cat without a human is excluded.

 human_name | cat_name
------------+-----------
 Abe        | Axel
 Jen        | Jellybean
 Jen        | Juniper

A FULL OUTER JOIN returns all humans and all cats, with NULL if there is no match on either side.

 human_name | cat_name
------------+-----------
 Abe        | Axel
 Ann        | [NULL]
 Ben        | [NULL]
 Jen        | Jellybean
 Jen        | Juniper
 [NULL]     | Bitty

A LEFT OUTER JOIN returns all humans (the left table). Any human without a cat gets a NULL in the cat_name column. Any cat without a human is excluded.

 human_name | cat_name
------------+-----------
 Abe        | Axel
 Ann        | [NULL]
 Ben        | [NULL]
 Jen        | Jellybean
 Jen        | Juniper

A RIGHT OUTER JOIN returns all cats (the right table). Any cat without a human gets a NULL in the human_name column. Any human without a cat is excluded.

 human_name | cat_name
------------+-----------
 Abe        | Axel
 Jen        | Jellybean
 Jen        | Juniper
 [NULL]     | Bitty

INNER vs OUTER

You can see that while an INNER JOIN gets only matching pairs, each kind of OUTER join includes some items without a match.

However, the actual words INNER and OUTER do not need to appear in queries:

• JOIN by itself implies INNER
• LEFT JOIN, RIGHT JOIN and OUTER JOIN all imply OUTER

The "outer" and "inner" are just optional elements, you are just dealing with two (three) kinds of joins. Inner joins (or what is the default when using only "join") is a join where only the elements that match the criteria are present on both tables.

The "outer" joins are the same as the inner join plus the elements of the left or right table that didn't match, adding nulls on all columns for the other table.

The full join is the inner plus the right and left joins.

In summary, if we have table A like this

And table B like this:

The inner join:

from tableA join tableB on tableA.idB = tableB.idB

Left outer join:

from tableA left join tableB on tableA.idB = tableB.idB

Right outer join:

from tableA right join tableB on tableA.idB = tableB.idB

Full outer join:

from tableA full join tableB on tableA.idB = tableB.idB