MySQL LEFT JOIN Keyword

  MySQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

Customers

And a selection from the “Orders” table:

Orders

MySQL LEFT JOIN Example

The following SQL statement will select all customers, and any orders they might have:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

MySQL LEFT JOIN with Group By Clause

The Left Join can also be used with the GROUP BY clause. The following statement returns customer id, customer name, qualification, price, and date using the Left Join clause with the GROUP BY clause.

Orders_1

Customers_1

SELECT customers_1.customer_id, cust_name, qualification, price, date  
FROM customers_1  
LEFT JOIN orders_1 ON customers_1.customer_id = orders_1.customer_id  
GROUP BY price;  

MySQL INNER JOIN Keyword

MySQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Demo Database

In this tutorial we will use the well-known sample database.

Below is a selection from the “Orders” table:

Orders

And a selection from the “Customers” table:

Customers

MySQL INNER JOIN Example

The following SQL statement selects all orders with customer information:

Example

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

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown.

JOIN Three Tables

The following SQL statement selects all orders with customer and shipper information:

Example

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

MySQL Joins

                                        MySQL Joins

MySQL Joining Tables

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

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

Orders

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

Customers

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example

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

Supported Types of Joins in MySQL

INNER JOIN

Compares all rows from all tables specified in a query with each other and returns records with matching values as a new result table.

LEFT OUTER JOIN

Returns the result table with all rows from the left table and only those rows from the right table that match the JOIN condition (non-matching rows are returned with NULLs).

RIGHT OUTER JOIN

Returns the result table with all rows from the right table and only those rows from the left table where the JOIN condition is fulfilled (non-matching rows are returned
with NULLs).

CROSS JOIN

Combines each row from one table with each row from another table and thus returns a new result table with all possible row combinations from each table.

FULL OUTER JOIN

MySQL does not directly support FULL OUTER JOIN that returns both matching and non-matching rows from the joined tables, but you can combine LEFT and RIGHT OUTER JOINs to achieve the same result.

SELF JOIN

Compares a row with other rows within the same table or extracts hierarchical data — table aliases should be used instead of repeating the same table name in a query.

MySQL Aliases

                                 MySQL Aliases

Aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax

SELECT column_name(s)
FROM table_name AS alias_name;

Demo Database

In this tutorial we will use the well-known Employees sample database.

Below is a selection from the “Employees” table:

Alias for Columns Examples

The following SQL statement creates two aliases, one for the EmployeeID column and one for the LastName column:

Example

SELECT EmployeeID AS ID, LastName AS Name
FROM Employees;

The following SQL statement creates two aliases, one for the LastName column and one for the FirstName column.

Note: Single or double quotation marks are required if the alias name contains spaces:

Example

SELECT LastName AS LName, FirstName AS "F Name"
FROM Employees;

The following SQL statement creates an alias named “Address” that combine two columns (Address, City):

Example

SELECT LastName, CONCAT_WS(', ', Address,City) AS Address
FROM Employees;

Alias for Tables Example

The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the “Customers” and “Orders” tables, and give them the table aliases of “c” and “o” respectively (Here we use aliases to make the SQL shorter):

Orders

Customers

Example

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

The following SQL statement is the same as above, but without aliases:

Example

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

Aliases can be useful when:

• There are more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
• Two or more columns are combined together