An SQL JOIN clause is used to fetch or extract records by multiple columns from two or more tables in a database, based on a common field between them.

Basically database tables are connected to each other with keys or values. We use these keys relationship to combine columns in our sql query.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Sample Database Table - Book1

BookId BookName Description
101 Sql Complete Reference It descripes how to write and execute SQL statement into database.
102 Sql Commands It exaplins a list of SQL command.
103 Pl Sql Quick Programming How to write and execute SQL programming using pl sql.
104 Sql Query Injection How to hack SQL queries in database.

Sample Database Table - Book2

BookId AuthorName DomainName BookPrice
101 Suresh Babu Sql Database 250.5
102 Siva Kumar Sql Database 120
103 Azagu Varshith Sql Programming 150
104 Bala Murugan Sql Hacking 199.99

Note:- There must be a common columns while selecting columns from multiple tables. In the "Book1" and "Book2" both tables contains a common column called "BookID".

SQL Joins - Example

Notice that the "BookID" column in the "Book1" table refers to the "BookID" in the "Book2" table. The relationship between the two tables above is the "BookID" column.

Then, if we run the following SQL statement (that contains an INNER JOIN):

SELECT Book1.BookId, Book1.BookName, Book2.AuthorName, Book2.BookPrice
FROM Book1 
ON Book1.BookId = Book2.BookId;

The result of above query is:

BookId BookName AuthorName BookPrice
101 Sql Complete Reference Suresh Babu 250.5
102 Sql Commands Siva Kumar 120
103 Pl Sql Quick Programming Azagu Varshith 150
104 Sql Query Injection Bala Murugan 199.99

Different Types Of SQL Joins

There are different kinds of joins, which have different rules for the query results they create.

Below is a list of SQL Join types:

  • INNER JOIN: Returns all records when there is at least one match in BOTH tables
  • LEFT JOIN: Return all records from the left table, and the matched records from the right table
  • RIGHT JOIN: Return all records from the right table, and the matched records from the left table
  • FULL JOIN: Return all records when there is a match in ONE of the tables