Nested queries and joins (practical) Flashcards
What is a nested query?
Its another select statement (inner select) which result are used in the outer statement to help retrieve the right data.
What is a nested query / inner select also called?
A subquery
How does an inner join work?
compares each row of a table with all the rows of another table joining the row where the join condition is met.
What is an implicit inner join?
join statement where you dont write join:
SELECT *
FROM Employee, Department
WHERE Employee.ssn = Department.mgr_ssn;
IN MYSQL write a join statement that uses implicit join
USE EasterDB;
SELECT *
FROM DEPENDENT D, EMPLOYEE E
WHERE D.ESSN = E.SSN;
What a natural join?
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
select DISTINCT Fname, Ssn as Essn, Pno
from EMPLOYEE
natural join WORKS_ON
Natural join requires the attributes to have the same name how can you make sure that happens
Use the alias functionality
SELECT SSN as ESSN FROM EMPLOYEE…
Natural join might return duplicates why is that?
Because it joins the rows together for each tuple with the condition. so if it can join two rows, they both have the attribute and it will join two times.
How can you prevent that the natural join output has duplicate rows?
With distinct
SELECT DISTINCT Fname, Lname
FROM EMPLOYEE
NATURAL JOIN DEPARTMENT
A row has information about an employee and the employee’s supervisor. How can you write a statement that shows a table with all the employees next to their supervisor in two columns?
Join the table on itself and take one instance of each
SELECT E.Lname, S.Lname
FROM EMPLOYEE AS E INNER JOIN EMPLOYEE AS S ON E.dno = S.dno WHERE E.ssn < S.ssn
ORDER BY E.ssn, S.ssn;
What is the difference between a left and right join?
They both keep the records of the first mentioned table and appends the rows that have matching attributes. They leave the rest of the attributes as null
the difference is which side the retained rows will appear, first (left) or last (right)
What does a full join do?
It retains all the rows from the tables and merges where the attributes match leaving null values on both sides.
What is the syntax for left join?
select * from EMPLOYEE
LEFT JOIN WORKS_ON on EMPLOYEE.Ssn = WORKS_ON.ESSN
What is the syntax for right join?
select * from EMPLOYEE
right JOIN WORKS_ON on EMPLOYEE.Ssn = WORKS_ON.ESSN
What is the syntax for full join?
SELECT * FROM Employee
