SQL INNER JOIN
The most frequently used and important of the joins is the SQL INNER JOIN.
The SQL INNER JOIN clause selects all records or rows from both tables (tableA and tableB) and returns a result table by combining field or column values of two tables that is limited to the records as long as where there is a match keys in both tables.
Related Links
SQL INNER JOIN Syntax
Syntax for Inner Join is as
SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
INNER JOIN table_name2
ON table1.column_name1 = table2.column_name1;
or
SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
JOIN table_name2
ON table1.column_name1 = table2.column_name1;
Note: INNER JOIN is the same as JOIN.
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | The Complete Guide to MySql | 120 | 2014 | Optimization | Dharan |
2 | Securing Oracle | 165 | 2013 | Administration | Balan |
3 | Natural language Query To SQL | 190 | 2014 | Programming | Ranjani Mai |
4 | SQL Pocket Guide | 160 | 2015 | Security | Siva Kumar |
6 | PHP And MySQL Bible | 90 | 2010 | Performance | Rishi Keshan |
Sample Database Table - BookOrder
BookID | OrderDate | Qty | DeliveryDate |
---|---|---|---|
1 | 15-04-2011 | 2 | 17-05-2011 |
2 | 15-05-2000 | 16 | 24-05-2000 |
3 | 17-09-1997 | 17 | 25-10-1997 |
4 | 20-08-2005 | 9 | 27-09-2005 |
5 | 11-06-1995 | 8 | 26-07-1995 |
Note:- There must be a common columns while selecting columns from multiple tables. In the "BookOrder" and "Books" both tables contains a common column called "ID".
SQL INNER JOIN - Example
Notice that the "BookID" column in the "Books" table refers to the "ID" in the "BookOrder" 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
Books.BookID, Books.BookName, BookOrder.Qty, BookOrder.OrderDate
FROM Books
INNER JOIN BookOrder
ON Books.BookID = BookOrder.BookID;
The result of above query is:
BookID | BookName | Qty | OrderDate |
---|---|---|---|
1 | The Complete Guide to MySql | 2 | 15-04-2011 |
2 | Securing Oracle | 16 | 15-05-2000 |
3 | Natural language Query To SQL | 17 | 17-09-1997 |
4 | SQL Pocket Guide | 9 | 20-08-2005 |
Note: If there are rows in the "Books" table that do not have matches in "BookOrder", these Books will NOT be listed.
Related Links