Inner Join in SQL Example - sql - sql tutorial - learn sql
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialWhat is Inner Join in SQL ?
What is Inner Join in SQL ?
- The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.
Syntax:
SELECT *
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
OR
SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialInner Join SQL Example
Inner Join SQL Example
select * from dbo.Students S INNER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
- Chances are, you've already written a statement that uses an SQL Server INNER JOIN. It is the most common type of join.
- SQL Server INNER JOINS return all rows from multiple tables where the join condition is met.
Syntax
- The syntax for the INNER JOIN in SQL Server (Transact-SQL) is:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
- The SQL Server INNER JOIN would return the records where table1 and table2 intersect.
Example
- Here is an example of an INNER JOIN in SQL Server (Transact-SQL):
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
- This SQL Server INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.
- Let's look at some data to explain how the INNER JOINS work:
- We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:
| Supplier_id | Supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
- We have another table called orders with three fields ( order _id, supplier _id, and order _date). It contains the following data:
| Order_id | Supplier_id | Order_date |
|---|---|---|
| 500125 | 10000 | 2003/05/12 |
| 500126 | 10001 | 2003/05/13 |
| 500127 | 10004 | 2003/05/14 |
- If we run the SQL Server SELECT statement (that contains an INNER JOIN) below:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
Result
| Supplier_id | name | Order_date |
|---|---|---|
| 10000 | IBM | 2003/05/12 |
| 10001 | Hewlett Packard | 2003/05/13 |
- The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.
Old Syntax
- As a final note, it is worth mentioning that the SQL Server INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialDifference Between equi Join and inner Join
Difference Between equi Join and inner Join
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL Joins
SQL Joins
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialAll SQL Joins
All SQL Joins
