What's new

Closed Sql join

Status
Not open for further replies.

saketnanga00

Addict
Joined
May 12, 2016
Posts
79
Reaction
64
Points
125
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.

-_localhost_phpMyAdmin_3.3.2deb1ubuntu1_-_Chromium.png

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 :

upload_2018-7-17_18-52-57.png

under naman ng orders table is :
- order_id
- supplier_id
- order_date

then lagyan din ng sample data's

upload_2018-7-17_18-55-55.png


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

upload_2018-7-17_19-5-11.png

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.

left_outer_join.gif
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

upload_2018-7-17_19-16-16.png

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
right_outer_join.gif
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

whew... nice refresher...
medyo limot ko na mga sql joins... :) :)

parequest ts... gawa ka ng join ng 3 or more tables o kaya combination ng sql joins para masaya... :)
 
Last edited by a moderator:
thanks ts. tanong ko lang pag wala ka inispecify na outer or inner sa left join mo. matic na outer yun diba sir?
 
Status
Not open for further replies.

Similar threads

Back
Top