What is the SQL LEFT JOIN and how it can be used with the database?
The SQL LEFT JOIN is used to get data from multiple tables. These tables must be related to each other via some columns. Though it is not necessary, usually these tables are related through primary key. That is why those databases are called Relational Databases (RDBMS).
SQL LEFT JOIN Statement Example:
Lets continue with our old example of having two tables, 'customer' and 'order' in your database. You want to get order number, order grand total from the customer.
|1||Jwalant||123 xyz email@example.com||123-456-7890|
|2||Piyush||456 abc firstname.lastname@example.org||123-456-7890|
|3||Hiren||789 pqr email@example.com||123-456-7890|
|4||Yogesh||123 abc firstname.lastname@example.org||123-456-7890|
You also want to get name, email and phone from customer for all those orders. Use following syntax of sql insert statement for that case:
SELECT O.id as order_number, C.name as name, C.email, C.phone, O.grand_total, FROM order as O LEFT JOIN customer as C ON O.customer_id = C.id;
|10001||Jwalantemail@example.com||123 xyz street||115.50||123-456-7890|
|10002||Jwalantfirstname.lastname@example.org||123 xyz street||330.00||123-456-7890|
|10004||Yogeshemail@example.com||123 abc street||550.00||123-456-7890|
As you can see above, I have used LEFT JOIN to get data from two tables. The difference between LEFT JOIN and INNER JOIN is clearly visible. While in the case of INNER JOIN, SQL statement did not return any data related to order 10003, in this case of LEFT JOIN, SQL Statement returned the order information with empty customer information.
So, it is proved that SQL LEFT JOIN returns all the rows from the LEFT table and data from the right table if there is a match for that record, otherwise it returns empty or no data.