saketnanga00
Addict
- Joined
- May 12, 2016
- Posts
- 79
- Reaction
- 64
- Points
- 114
Hello dyan? hahaha matagal tagal na rin. pero anywys tama yang nababasa nyo sql join and ituturo ko sainyo ngayon. ano nga ba ito? ayon sa wikipedia ang sql join daw ay "An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. " gets nyo? madali lang naman intindihin ang main function lang naman neto ay pagsamahin ang dalawang table with same id or depende.
May tatlong different types eto
- unang una ay ang inner join, hmm eto yung pinakasimple lang sa tatlo
- left outer join or sometimes called LEFT JOIN
- right outer join or sometimes called RIGHT JOIN
take note : gagawin natin eto sa localhost/phpmyadmin after natin maopen ang wamp o xampp o kaya kung ano man ang gamit nyo.
Dyan po para malinaw.
So gawa muna tayo ng database kayo ng bahala sa name ng database, then gawa tayo dalawang table so as example pangalanan na lang natin yung table na suppliers at table orders.
so under suppliers table
- supplier_id
- supplier_name
then lagyan narin natin ng sample data's parang ganto :
under naman ng orders table is :
- order_id
- supplier_id
- order_date
then lagyan din ng sample data's
INNER JOIN (simple join)
So gaya nga ng sabi ko inner join eto yung pinakasimpleng type ng sql join, pagsasamahin neto lahat ng fields so meaning magjojoin lang ang dalawang table.
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
ayan ang syntax, ngayon naman ay gawin natin eto sa nagawa nating table sa taas. sundan nyo lang ako.
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
ang resulta nyan ay dapat maging ganto
Kung mapapansin natin yung rows for Microsoft and NVIDIA na galing sa supplier table ay hindi kasama, since the supplier_id's 10002 and 10003 ay hindi nagexist sa dalawang table, gets? yung row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.
LEFT OUTER JOIN
This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal.
Ayan mas madali ipaliwanag ng ganyan.
Syntax
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
Example
So panibagong halimbawa siguro
supplier table
supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
order table
order_id supplier_id order_date
500125 10000 2013/05/12
500126 10001 2013/05/13
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
ganto ang magiging resulta
yung rows for Microsoft and NVIDIA ay nakasama dahil yung LEFT OUTER JOIN ang ginamit. However, mapapansin natin na may null value yung dalawa sa huli sa kadahilanang walang same supplier id ang suppliers table sa orders table. gets?
RIGHT OUTER JOIN
same lang rin naman halos sa left outer join, pinagkaiba lang eto nakadepende naman sa kanan na table.
Syntax
The syntax for the RIGHT OUTER JOIN in MySQL is:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Example ulit
supplier table
supplier_id supplier_name
10000 Apple
10001 Google
order table
order_id supplier_id order_date
500125 10000 2013/08/12
500126 10001 2013/08/13
500127 10002 2013/08/14
If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
ganto ang magiging resulta nyan
order_id order_date supplier_name
500125 2013/08/12 Apple
500126 2013/08/13 Google
500127 2013/08/14 <null>
The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a <null> value.
May tatlong different types eto
- unang una ay ang inner join, hmm eto yung pinakasimple lang sa tatlo
- left outer join or sometimes called LEFT JOIN
- right outer join or sometimes called RIGHT JOIN
take note : gagawin natin eto sa localhost/phpmyadmin after natin maopen ang wamp o xampp o kaya kung ano man ang gamit nyo.
Dyan po para malinaw.
So gawa muna tayo ng database kayo ng bahala sa name ng database, then gawa tayo dalawang table so as example pangalanan na lang natin yung table na suppliers at table orders.
so under suppliers table
- supplier_id
- supplier_name
then lagyan narin natin ng sample data's parang ganto :
under naman ng orders table is :
- order_id
- supplier_id
- order_date
then lagyan din ng sample data's
INNER JOIN (simple join)
So gaya nga ng sabi ko inner join eto yung pinakasimpleng type ng sql join, pagsasamahin neto lahat ng fields so meaning magjojoin lang ang dalawang table.
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
ayan ang syntax, ngayon naman ay gawin natin eto sa nagawa nating table sa taas. sundan nyo lang ako.
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
ang resulta nyan ay dapat maging ganto
Kung mapapansin natin yung rows for Microsoft and NVIDIA na galing sa supplier table ay hindi kasama, since the supplier_id's 10002 and 10003 ay hindi nagexist sa dalawang table, gets? yung row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.
LEFT OUTER JOIN
This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal.
Ayan mas madali ipaliwanag ng ganyan.
Syntax
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
Example
So panibagong halimbawa siguro
supplier table
supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
order table
order_id supplier_id order_date
500125 10000 2013/05/12
500126 10001 2013/05/13
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
ganto ang magiging resulta
yung rows for Microsoft and NVIDIA ay nakasama dahil yung LEFT OUTER JOIN ang ginamit. However, mapapansin natin na may null value yung dalawa sa huli sa kadahilanang walang same supplier id ang suppliers table sa orders table. gets?
RIGHT OUTER JOIN
same lang rin naman halos sa left outer join, pinagkaiba lang eto nakadepende naman sa kanan na table.
Syntax
The syntax for the RIGHT OUTER JOIN in MySQL is:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Example ulit
supplier table
supplier_id supplier_name
10000 Apple
10001 Google
order table
order_id supplier_id order_date
500125 10000 2013/08/12
500126 10001 2013/08/13
500127 10002 2013/08/14
If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
ganto ang magiging resulta nyan
order_id order_date supplier_name
500125 2013/08/12 Apple
500126 2013/08/13 Google
500127 2013/08/14 <null>
The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a <null> value.
Attachments
-
You do not have permission to view the full content of this post. Log in or register now.