MySQL INNER JOIN

Inner join is a simple join which is based on two or multiple tables. We use the keyword INNER JOIN ON to make the inner join table. WE also can use the WHERE clause to make the join between tables which we can get the same return data as we use the INNER JOIN ON.

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

• First, you need to specify the tables you want to join with the main table. The main table appear in the FROM clause. The table you want to join with appear after keyword INNER JOIN. Theoretically, you can join a table with unlimited number of tables. However, for better performance you should limit the number of tables to join based on join conditions and volume of data in those tables.
• Second, you need to specify the join condition or join predicate. The join condition appears after the keyword ON of MySQL INNER JOIN clause. The join condition is the rule for matching rows between the main table and other tables being joined with.

The syntax of the MySQL INNER JOIN is as follows:

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

For example, if you join two tables A and B, the MySQL INNER JOIN clause compares each record of the table A with each record of table B to find all pair of records that satisfy the join-condition. When the join-condition are satisfied, column values for each matched pair of record of table A and table B are combined into a returned record. Note that the records on both tables have to match based on the join-condition. If no record on both table A and B matches, the query will return an empty result.

AVOID COLUMN AMBIGUOUS ERROR IN MYSQL INNER JOIN

If you join multiple tables that has column with similar name, you have to use table qualifier to refer to column to avoid column ambiguous error. Suppose if table tbl_A and tbl_B has the same column M. In the SELECT statement with MySQL INNER JOIN clause, you have to refer to column M by using the table qualifier as tbl_A.M or tbl_B.M (table_name.column_name).

Another effective way to avoid column ambiguous is by using table alias. For example, you can give A as the table alias of the table tbl_A and refer to the column M as A.M so you don’t have to type again and again the long table name in your SQL statement.

EXAMPLE OF MYSQL INNER JOIN CLAUSE

Let’s take a look at two tables: products and orderDetails in our sample database.

The products table is the master data table that stores all products. Whenever a product is sold, it is stored in the orderDetails table with other information. The link between products table and orderDetails table is productCode.

Now, if you want to know what product was sold in which order, you can use the MySQL INNER JOINclause as follows:
SELECT A.productCode, A.productName, B.orderNumber
FROM products A
INNER JOIN orderDetails B
on A.productCode = B.productCode LIMIT 10;


The MySQL INNER JOIN clause compares each row of table products and orderDetails table to find a pair of rows that has the same productCode. If a pair of rows that have the same, the product code, product name and order number are combined into a returned row.

MYSQL LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

The MySQL LEFT JOIN clause consists of LEFT JOIN keyword followed by the second table you want to join. Next element is the ON keyword followed by the join condition. In the join condition, you specify the columns to be used for matching row in the two tables. The syntax of MySQL is as follows:
SELECT t1.c1, t1.c2,…t2.c1,t2.c2
FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c1 …(join_condition)
WHERE where_condition


The MySQL LEFT JOIN clause works like this: when a row from the left table matches a row from the right table based on join_condition, the row’s content are selected as an output row. When row in the left table has no match it is still selected for output, but combined with a “fake” row from the right table that contains NULL in all columns. In short, the MySQL LEFT JOIN clause allows you to select all rows from the left table even there is no match for them in the right table.

Let’s take a look at two table customers and orders: If you want to know which customer has which order and each order’s status. You can use the MySQL LEFT JOIN as follows:
SELECT c.customerNumber, customerName,orderNUmber, o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber LIMIT 10;

The left table is the customers table so you see all customers are listed as the way MySQL LEFT JOIN clause works. However, there are rows that we have customer information but all order information are NULL. This means those customers do not have any order in our database. The MySQL LEFT JOIN clause is very useful when you want to find the records in the left table that are unmatched by the right table. You can accomplish this by add a WHERE clause to select only that have NULL values in a right table column. So to find all customers who does not have any order in our database we can use the MySQL LEFT JOIN clause as follows: