Understanding the Concept of SQL Inner Joins

Understanding the Concept of SQL Inner Joins

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.

inner_join

Let’s have a look at a selection from the “Orders” tables:

OrderID CustomerID OrderDate
10308 2 1997-09-18
10309 37 1997-09-19
10310 77 1997-09-20

Then, have a look at a selection from the other “Customers” table:

CustomerID CustomerName ContactName Country
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):

Example:-

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

The result will came something like this:-

OrderID CustomerName OrderDate
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
10278 Berglunds snabbköp 8/12/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:
  • inner join,
  • sql inner joins,
  • sql join,
  • sql joins queries,
  • Leave a Reply