SQL JOINS

Think of your database as a city.

Your tables are the different buildings; the library, the hospital, the post office. Each has their own records, their own purpose, their own filing system.

A primary key is the address of the building, a foreign key is a note saying "see the hospital on 5th street" and a JOIN is the road that connects them.

Without these connecting roads, otherwise known as join clauses, your city is pretty much useless as each building is isolated. By joining two or more tables you can answer questions that required information from more than one table, like "which patients checked out medical books from the library?"

In structured query language (SQL), a join is used to join two or more tables in a relational database. As their name suggests, relational databases are ones that store data according to pre-defined relationships, which define how data that is stored in one table connects to that which is stored in another (or several others).

The join clause is used to retrieve data from related tables in a database. Because it retrieves data from multiple tables, the SQL join clause is much more complex than a simple query that retrieves data from just one.

TYPES OF SQL JOINS

1. Inner join

An inner join combines two tables by using a key. For instance, the key might be a column called “product id” where each user has a unique number. You could combine this table with another that contains information about each user by joining on the “product id” column. The following example uses an inner join clause to join two tables:In this example we are joining the drivers_trips table to safari.drivers on common column driver_id.

2. Left outer join
The left outer joins return all rows from the first table and only those rows from the second table that match,the tables are joined on a common column.Left outer join and Left join is one and the same thing .Here is an example of how to use the Left outer join clause to join two tables:

3. Right outer join
Right joins are logical opposites of left joins: they return all rows from the second table, and only the rows in the first table that match.Left outer join and Left join is one and the same thing,both of them can be used interchangeably. This example shows how to use a right outer join clause to join two tables:

4. Full outer join
Full joins combine both left and right joins, and return all rows from both tables, provided that both have at least one matching row. Here is an example of using the full outer join clause to join two tables:

To visually understand what is how join happens take a look at the following table.