What is the SQL INNER JOIN Statement and how it can be used with the database?
The SQL INNER JOIN statemet is used to get data from multiple tables in the database. INNER JOIN will return the data from both tables if there is at leaset one match.
SQL INNER 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 INNER 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||Piyushemail@example.com||789 pqr street||550.00||123-456-7890|
As you can see above, I have used INNER JOIN to get data from two tables.
It returns only orders with Id 10001, 10002 and 10004. It does not return order 10003 because, SQL did not find any customer in the customer table matching with id 5.
Same way, it does not return any data related to customers 'Hiren' and 'Yogesh' as there is no order from these two customers.
Hence, it is proved that SQL INNER JOIN returns only those data from both table where there is at least one match.