In the database, we can’t have all the information into one flat table. Reason being the concern over size of data, several columns etc. For this, database experts suggest to have different tables. However, while extracting the data for reports – one can’t find all the data in one single table. To convert data into information – we need to extract the relevant data from several tables.
Here comes the role of joins which enables us to collect data from different tables.
SQL Join means combination of two or more tables in a database. It create a set that can be saved as a table as it is. And In other words join means for combining fields from two or more tables by using values common to each.
Different type of SQL JOINs
Before we start with examples, we will short explain you the different type of SQL Joins. Which we can use at the time of using SQL.
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables
- LEFT JOIN: Return all rows from the left table, and the matched rows from the right tables
- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left tables
- FULL JOIN: Return all rows when there is a match in ONE of the tables
In all above joins 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.
INNER JOIN is the same as JOIN.
Let’s have a look at a selection from the “Orders” tables:
Then, have a look at a selection from the other “Customers” table:
|1||Alfreds Futterkiste||Maria Anders||Germany|
|2||Ana Trujillo Emparedados y helados||Ana Trujillo||Mexico|
|3||Antonio Moreno Taquería||Antonio Moreno||Mexico|
Notice that the “CustomerID” column in the “Orders” table refers to the customer in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.
Then, if we run the following SQL statement (that contains an INNER JOIN):
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
INNER JOIN Customers
The result will came something like this:-
|10308||Ana Trujillo Emparedados y helados||9/18/1997|
|10365||Antonio Moreno Taquería||11/27/1997|
|10383||Around the Horn||12/16/1997|
|10355||Around the Horn||11/15/1997|
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the “Customers” table that do not have matches in “Orders”, these customers will NOT be listed.Tags: