Inner Join 4 Tabel

Sql Inner Join 4 Tables Or More Tables Moinrazakhan Web

SQL INNER JOIN

Summary: in this tutorial, you will learn how to query data from multiple tables using SQL INNER JOIN statement.

In the previous tutorial, you learned how to query data from a single table using the SELECT statement. However, you often want to query data from multiple tables to have a complete result set for analysis. To query data from multiple tables you use join statements.

SQL provides several types of joins such as inner join, outer joins ( left outer join or left join, right outer join or right join, and full outer join) and self join. In this tutorial, we will show you how to use the INNER JOIN clause.

SQL INNER JOIN syntax

The following illustrates INNER JOIN syntax for joining two tables:

SELECT

column1, column2

FROM

table_1

INNER JOIN table_2 ON join_condition;

Let’s examine the syntax above in greater detail:

The table_1 and table_2 are called joined-tables.
For each row in the table_1, the query find the corresponding row in the table_2 that meet the join condition. If the corresponding row found, the query returns a row that contains data from both tables. Otherwise, it examines next row in the table_1, and this process continues until all the rows in the table_1 are examined.

For joining more than two tables, the same logic applied.

SQL INNER JOIN examples

SQL INNER JOIN – querying data from two tables example

In this example, we will use the products and categories tables in the sample database. The following picture illustrates the database diagram.

In the diagram above:

One category can have many products.
One product belongs to one and only one category.

Therefore, there is a many-to-one relationship between the rows in the categories table and rows in the products table. The link between the two tables is the categoryid column.

We need to query the following data from both tables:

productID, productName from the products table.
categoryName from the categories table.

The following query retrieves data from both tables:

SELECT

productID, productName, categoryName

FROM

products

INNER JOIN

categories ON categories.categoryID = products.categoryID;

The join condition is specified in the INNER JOIN clause after the ON keyword as the expression:

categories.categoryID = products.categoryID

For each row in the products table, the query finds a corresponding row in the categories table that has the same categoryid. If there is a match between two rows in both tables, it returns a row that contains columns specified in the SELECT clause i.e., product id, product name and category name; otherwise, it checks the next row in products table to find the matching row in the categories table. This process continues until the last row of the products table is examined.

SQL INNER JOIN – querying data from three tables

We can use the same techniques for joining three tables. The following query selects productID, productName, categoryName and supplier from the products, categories and suppliers tables:

SELECT

productID,

productName,

categoryName,

companyName AS supplier

FROM

products

INNER JOIN

categories ON categories.categoryID = products.categoryID

INNER JOIN

suppliers ON suppliers.supplierID = products.supplierID

Implicit SQL INNER JOIN

There is another form of the INNER JOIN called implicit inner join as shown below:

SELECT

column1, column2

FROM

table_1,

table_2

WHERE

join_condition;

In this form, you specify all joined-tables in the FROM clause and put join condition in WHERE clause of the SELECT statement. We can rewrite the query example above using the implicit INNER JOIN as follows:

SELECT

productID, productName, categoryName

FROM

products,

Visualize INNER JOIN using Venn diagram

We can use the Venn diagram to illustrates how the INNER JOIN works. The SQL INNER JOIN returns all rows in table 1 (left table) that have corresponding rows in table 2 (right table).

In this tutorial, we have shown you how to use the SQL INNER JOIN clause to select data from two or more tables based on a specified join condition.