name: portada layout: true class: portada-slide, middle, right --- # DML: Joins ## Databases .footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))] --- layout: true class: regular-slide .right[.logo[]] --- # Joins A JOIN clause is used to combine rows from two or more tables. .center[[Joins W3](https://www.w3schools.com/sql/sql_join.asp)] .columns[ .column[   ] .column[   ] ] --- # Data example The following tables and data will be used as an example: .columns[ .column[ - Table __group__: | id | name | tutorDNI | | :-: | :-: | :-: | | 1 | "DAW" | "00000000A" | | 2 | "DAM" | "00000001B" | | 3 | "ASIX" | "00000003D" | | 4 | "SMR" | NULL | ] .column[ - Table __teacher__: | DNI | name | | :-: | :-: | | "00000000A" | "Pere" | | "00000001B" | "Marta" | | "00000002C" | "Laura" | | "00000003D" | "Montse" | ] ] --- # INNER JOIN Returns records that have matching values in both tables.  ``` SELECT g.name `group`, t.DNI, t.name `tutor` FROM `group` g INNER JOIN teacher t --> Table to be joined ON g.tutorDNI = t.DNI; --> Attribute/s that link both tables ``` ```text +-------+-----------+--------+ | group | DNI | tutor | +-------+-----------+--------+ | DAW | 00000000A | Pere | | DAM | 00000001B | Marta | | ASIX | 00000003D | Montse | +-------+-----------+--------+ ``` --- # LEFT JOIN Returns all records from the left table, and the matched records from the right table.  ``` SELECT g.name `group`, t.DNI, t.name `tutor` FROM `group` g LEFT JOIN teacher t --> Table to be joined ON g.tutorDNI = t.DNI; --> Attribute/s that link both tables ``` ```text +-------+-----------+--------+ | group | DNI | tutor | +-------+-----------+--------+ | DAW | 00000000A | Pere | | DAM | 00000001B | Marta | | ASIX | 00000003D | Montse | | SMR | NULL | NULL | +-------+-----------+--------+ ``` --- # RIGHT JOIN Returns all records from the right table, and the matched records from the left table.  ``` SELECT g.name `group`, t.DNI, t.name `tutor` FROM `group` g RIGHT JOIN teacher t --> Table to be joined ON g.tutorDNI = t.DNI; --> Attribute/s that link both tables ``` ```text +-------+-----------+--------+ | group | DNI | tutor | +-------+-----------+--------+ | DAW | 00000000A | Pere | | DAM | 00000001B | Marta | | ASIX | 00000003D | Montse | | NULL | 00000002C | Laura | +-------+-----------+--------+ ``` --- # FULL JOIN Returns all records when there is a match in either left or right table  ``` SELECT g.name `group`, t.DNI, t.name `tutor` FROM `group` g FULL JOIN teacher t --> Table to be joined ON g.tutorDNI = t.DNI; --> Attribute/s that link both tables ``` ```text +-------+-----------+--------+ | group | DNI | tutor | +-------+-----------+--------+ | DAW | 00000000A | Pere | | DAM | 00000001B | Marta | | ASIX | 00000003D | Montse | | SMR | NULL | NULL | | NULL | 00000002C | Laura | +-------+-----------+--------+ ```