What is the SQL JOIN and how it can be used with the database?
The SQL 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 JOIN Statement Example:
Assume that you have a table '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|
|10003||Piyushemail@example.com||789 pqr street||495.50||123-456-7890|
|10004||Yogeshfirstname.lastname@example.org||123 abc street||550.00||123-456-7890|
As you can see above, I have used LEFT JOIN to get data from two tables. In the next tutorial, you will learn about all type of joins in SQL in detail. Those are: