Pye eo MYSQL JOINS THE KEY 4 bem omy yy Unlocking the Power of Joins in ETE SET Databases 7 mS -s ro pensSELFJOIN = ts: A SELF JOIN is a peeesatl ee footet but the table is joined with itself. It is useful when you want to compare rows in the Same table ‘ Ee a STUDENT FRIEND coors re WIRE SELECT A.Student Student, B.Student Friend FROM friend A, friend B WHERE A.ID = B.FRIEND_ID} jswow oy co Cs ro. a ro pensS|) | A CROSS JOIN returns nats Cartesian product of two tables (all possible combinations of Teoh ue} from both tables) | Useful for Pore tetstss PVT eee Weinioisig two BAe SYNTAX SELECT columns © FROM tablet seh JOIN table2; EXAMPLE SELECT Products. ered. Gategories.CategoryName FROM Products an CROSS JOIN Categories; 7 Pen : TABLET) | TABLE2INNER JOIN. An INNER JOIN returns aoe the rows that have matching values in both tables. To retrieve data that exists in both tables and where the relationship po the two ede rh satisfied INNER JOIN SYNTAX SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column; EXAMPLE SELECT Orders.OrderID, Customers .CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = CUS USTIES (GES ener eS TABLE1 (IM) TABLE2 ~~ 3 Reo & PeayA) : A LEFT JOIN returns all records from the left table (table1) and the matched records from the right table (table2). If no match exists, NULL values are returned for columns from idelBat-denmeclocn 3 To include all rows from the oa table, regardless of whether they have matching rows in the right table. SYNTAX SELECT columns - FROM table1 LEFT JOIN table2 ON table1.column = table2.column; EXAMPLE SELECT Orders.OrderID, Customers.€ustomerName FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers. CustomerID; LEFT JOIN TABLET) TABLEZ ae Reel as PersRIGHT JOIN St : A RIGHT JOIN returns all records from the right table (table2) and the matched recoids from the left table (table1). If no match exists, NULL values are returned for columns from the left table. To include all rows from the right table, regardless of whether they have matching rows in the left table. SYNTAX SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; Fi EXAMPLE SELECT Orders.OrderID, Customers.€ustomerName FROM Orders RIGHT .JOIN Customers ON Orders.CustomerID = Customers. CustomerID; 2 RIGHT JOIN TABLE ae ~~ : Reel 2 sa PersTt ae The UNION operator Pronlrrhat Sci acy marcecoran oc SELECT statements. It removes duplicate records by default. Used to combine results from. Peanitaes SELECT teste into a single dataset. vee SELECT column_names FROM table1 UNION SEREGE ee FROM table2; PFW aD SELECT City FROM areaeel ta 2 UNION . SELECT City FROM Suppliers; sayan = +e esasSRD 8) | A FULL OUTER JOIN ¢eturns all records when there isa match in either the left table or the right table. If there’s no match, NULL values are returned for missing matches. To retrieve all rows from both tables, showing NULL for non-matching rows. SYNTAX (SELEGIE® TABLE1 TABLE2 el ee Sena mle Cyd ON tablel.id = table2.id)- UNION mi (SELECT * FROM table1 RIGHT JOIN table2 ON i tablel-id = table2.id); eV Ae — SELECT Customers.CustomerName, Orders. OrderID. fae metered i aes) eae ae Customers.CustomerID = Orders.CustomerID cee State Melo eS ta CU ea CRC a Ct) FROM Customers a RIGHT JOIN Orders ON Customers.CustomerID = ae ke asad Pon 3 Reo & PeayPROBLEM: A Fe DUE OCLC ESCORTS Ue CT CORR ACCTI PSO Ue eS Ce) matching customer. UTC Ae Seen! Pe e OrderiD rT cod 103 cra} Soest ceed 1 e oy ry cord a ro : pensPROBLEM: . SOE CR ORG a tes and UES ROSE CT ESE DT UCR HR Me CI) employees who are not assigned. to any Pe eR UTC aR Sod ores ed Dee)PROBLEM: A Fe You have two tables: Books and Authors. You need to list all books along with their . Pee RU Ree OE U EUS One AR Ren ris TRU am A A ped) rT Ey Ponda urePROBLEM: A Fe OER CH Tse UTE TES You need to list all students and the courses UCSC Mee STE and all courses, even if there-is no match Pe RTS eS Jf aPC cd Src) r BA 3 aoe fermen) [ene ad cur ry ctv Cac se) Poa pens TiaPROBLEM: A Fe STE CLC ON RUE UT WCE Cte TUR Ce We WC Scr LC UT aC OL CTT ares Mit Hs 2 tae Seat so cone opera fen) ol red A B co rue 5 Cer B Poa pens