Sql Left Join Keyword

Summary: in this tutorial, you will learn about MySQL LEFT JOIN clause & how to lớn apply it lớn query data from two or more tables.

Bạn đang xem: Sql left join keyword

Introduction khổng lồ MySQL LEFT JOIN clause

The LEFT JOIN allows you to lớn query data from two or more tables. Similar lớn the INNER JOIN clause, the LEFT JOIN is an optional clause of the SELECT statement, which appears immediately after the FROM clause.

Suppose that you want to lớn join two tables t1 and t2.

The following statement shows how to use the LEFT JOIN clause to lớn join the two tables:


SELECT select_listFROM t1LEFT JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)When you use the LEFT JOIN clause, the concepts of the left table và the right table are introduced.

In the above syntax, t1 is the left table và t2 is the right table.

The LEFT JOIN clause selects data starting from the left table (t1). It matches each row from the left table (t1) with every row from the right table(t2) based on the join_condition.

If the rows from both tables cause the join condition evaluates to lớn true, the LEFT JOIN combine columns of rows from both tables lớn a new row & includes this new row in the result rows.

In case the row from the left table (t1) does not match with any row from the right table(t2), the LEFT JOIN still combines columns of rows from both tables into a new row và includes the new row in the result rows. However, it uses NULL for all the columns of the row from the right table.

In other words, LEFT JOIN returns all rows from the left table regardless of whether a row from the left table has a matching row from the right table or not.

If there is no match, the columns of the row from the right table will contain NULL.

The following Venn diagram helps you visualize how the LEFT JOIN clause works:


*
MySQL LEFT JOIN – Venn Diagram

MySQL LEFT JOIN clause examples

Let’s take some examples of using the LEFT JOIN clause.

1) Using MySQL LEFT JOIN clause to lớn join two tables

See the following tables customers and orders in the sample database.

Xem thêm: Của Năm Không Nhuận Có Bao Nhiêu Ngày, Năm Không Nhuận Có Bao Nhiêu Ngày?

*

Each customer can have zero or more orders while each order must belong to lớn one customer.

This query uses the LEFT JOIN clause to find all customers & their orders:


SELECT customers.customerNumber, customerName, orderNumber, statusFROM customersLEFT JOIN orders ON orders.customerNumber = customers.customerNumber;
Code language: SQL (Structured Query Language) (sql)Alternatively, you can save some typing by using table aliases:


SELECT c.customerNumber, customerName, orderNumber, statusFROM customers cLEFT JOIN orders o ON c.customerNumber = o.customerNumber;
Code language: SQL (Structured Query Language) (sql)

*
In this example:

The customers is the left table and orders is the right table.The LEFT JOIN clause returns all customers including the customers who have no order. If a customer has no order, the values in the column orderNumber and status are NULL.

Because both table customers and orders have the same column name ( customerNumber) in the join condition with the equal operator, you can use the USING syntax as follows:


SELECTcustomerNumber,customerName,orderNumber,statusFROMcustomersLEFT JOIN orders USING (customerNumber);
Code language: SQL (Structured Query Language) (sql)The following clauses are equivalent:


USING (customerNumber)
Code language: SQL (Structured Query Language) (sql)And


ON c.customerNumber = o.customerNumber
Code language: SQL (Structured Query Language) (sql)If you replace the LEFT JOIN clause by the INNER JOIN clause, you will get the only customers who have at least one order.

2) Using MySQL LEFT JOIN clause to find unmatched rows

The LEFT JOIN clause is very useful when you want to lớn find rows in a table that doesn’t have a matching row from another table.

The following example uses the LEFT JOIN khổng lồ find customers who have no order:


SELECT c.customerNumber, c.customerName, o.orderNumber, o.statusFROM customers cLEFT JOIN orders o ON c.customerNumber = o.customerNumberWHERE orderNumber IS NULL;
Code language: SQL (Structured Query Language) (sql)

*

3) Using MySQL LEFT JOIN lớn join three tables

See the following three tables employees, customers, và payments:

*

This example uses two LEFT JOIN clauses to lớn join the three tables: employees, customers, và payments.


SELECT lastName, firstName, customerName, checkNumber, amountFROM employeesLEFT JOIN customers ON employeeNumber = salesRepEmployeeNumberLEFT JOIN payments ON payments.customerNumber = customers.customerNumberORDER BY customerName, checkNumber;
Code language: SQL (Structured Query Language) (sql)This picture shows the partial output:

*

How it works.

The first LEFT JOIN returns all employees and customers who represented each employee or NULL if the employee does not in charge of any customer.The second LEFT JOIN returns payments of each customer represented by an employee or NULL if the customer has no payment.

Condition in WHERE clause vs. ON clause

See the following example.


SELECT o.orderNumber, customerNumber, productCodeFROM orders oLEFT JOIN orderDetails USING (orderNumber)WHERE orderNumber = 10123;
Code language: SQL (Structured Query Language) (sql)This example used the LEFT JOIN clause to query data from the tables orders và orderDetails. The query returns the order & its line items of the order number 10123.

*

However, if you move the condition from the WHERE clause to the ON clause:


SELECT o.orderNumber, customerNumber, productCodeFROM orders oLEFT JOIN orderDetails d ON o.orderNumber = d.orderNumber & o.orderNumber = 10123;
Code language: SQL (Structured Query Language) (sql)It will have a different meaning.

In this case, the query returns all orders but only the order 10123 will have line items associated with it as in the following picture:

*

Notice that for INNER JOIN clause, the condition in the ON clause is equivalent khổng lồ the condition in the WHERE clause.

In this tutorial, you have learned how lớn use the MySQL LEFT JOIN clause to lớn join data from two or more tables.

link tải 567 live app | W88Vuive | tải app qqlive apk |

https://789betvi.co/