SQL JOIN

SQL INNERJOIN

Before using MySQL INNER JOIN clause, you have to specify the following criteria:

  • First, you have to specify the main table that appears in the FROM clause.
  • Second, you need to specify the table that you want to join with the main table, which appears in the INNER JOIN clause. Theoretically, you can join a table with many tables. However, for better query performance, you should limit the number of tables to join.
  • Third, you need to specify the join condition or join predicate. The join condition appears after the keyword ON of the INNER JOIN clause. The join condition is the rule for matching rows between the main table and the other tables.

The syntax of the MySQL INNER JOIN clause is as follows:

Syntax

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

Let’s simplify the syntax above by assuming that we are joining two tables T1 and T2 using the INNER JOIN clause.
For each record in the T1 table, the MySQL INNER JOIN clause compares it with each record of the T2 table to check if both of them satisfy the join condition. When the join condition is matched, it will return that record that combine columns in either or both T1 and T2 tables.
Notice that the records on both T1 and T2 tables have to be matched based on the join condition. If no match found, the query will return an empty result set.
The logic is applied if we join more than 2 tables.
For Creating Order,orderdetails, customer Table Click Here

Query:- Get Total Quality Order from Order Table with respect Order Details Table.

SELECT T1.orderNumber,
STATUS , SUM( quantityOrdered * priceEach ) total
FROM orders AS T1
INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber

Left Join

The MySQL LEFT JOIN clause allows you to query data from two or more database tables. The LEFT JOIN clause is an optional part of the SELECT statement, which appears after the FROM clause.
Let’s assume that we are going to query data from two tables T1 and T2. The following is the syntax of the LEFT JOIN clause that joins the two tables:

Syntax

SELECT T1.c1, T1.c2,... T2.c1,T2.c2
FROM T1
LEFT JOIN T2 ON T1.c1 = T2.c1...

Left Join Example:

SELECT c.customerNumber, c.customerName, orderNumber, o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE orderNumber IS NULL

Admin has written 171 articles