[IBM]Databases and SQL for Data Science with Python - JOIN Statements

* Join Operator: for the relationship among the entities, you need to use the JOIN operator. 

* Primary Key: uniquely identifies each row in a table 

* Foreign Key: refer to a primary key of another table

 

* Inner Join: displays matches only ( 1 primary key and 1 Foreign key ) 

select b.borrower_id, b.lastname, b.country, l.borrower_id, l.loan_date
from borrower b inner join loan l 
on b.borrower_id = l.borrower_id 

• The borrower table is the left table. 
• Each column name is prefixed with an alias to indicate which table each column is associated with. 
• Return only the matching values 

 

 

 

 

* Outer Join: left, right, full outer joins 

: returns also the rows not matching the between the tables

* Left join 

* Right Join

* Full Join

#How does a CROSS JOIN (also known as Cartesian Join) statement syntax look?

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

#How does an INNER JOIN statement syntax look?

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
WHERE condition;

#How does a LEFT OUTER JOIN statement syntax look?

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

#How does a RIGHT OUTER JOIN statement syntax look?

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

#How does a FULL OUTER JOIN statement syntax look?

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

#How does a SELF JOIN statement syntax look?

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

 

EXERCISE 

--Select the names and job start dates of all employees who work for the department number 5.
SELECT e.F_NAME, h.START_DATE 
from EMPLOYEES as e left join JOB_HISTORY as h 
on e.EMP_ID = h.EMPL_ID 
where e.DEP_ID = '5';


--Select the names, job start dates, and job titles of all employees who work for the department number 5.
select e.F_NAME, e.L_NAME, h.START_DATE, h.JOB_TITLE 
from EMPLOYEES as e left join (select jh.START_DATE, jb.JOB_TITLE, jh.EMPL_ID from JOB_HISTORY as jh full join JOBS as jb on jh.JOBS_ID = jb.JOB_IDENT) as h
on e.EMP_ID =h.EMPL_ID
where e.DEP_ID='5';

select e.F_NAME, e.L_NAME, jh.START_DATE, j.JOB_TITLE 
from EMPLOYEES as e 
INNER JOIN JOB_HISTORY as jh on e.EMP_ID = jh_EMPL_ID
INNER JOIN JOBS as J on e.JOB_ID=j.JOB_IDENT
where e.DEP_ID='5';


--Perform a Left Outer Join on the EMPLOYEES and DEPARTMENT tables and select employee id, last name, department id and department name for all employees.
select e.EMP_ID, e.L_NAME, e.DEP_ID, d.DEP_NAME 
from EMPLOYEES as e left outer join DEPARTMENTS as d 
on e.DEP_ID = d.DEPT_ID_DEP;


--Re-write the previous query but limit the result set to include only the rows for employees born before 1980.
select e.EMP_ID, e.L_NAME, e.DEP_ID, d.DEP_NAME 
from EMPLOYEES as e left outer join DEPARTMENTS as d 
on e.DEP_ID = d.DEPT_ID_DEP
where year(e.B_DATE) < 1980;


--Re-write the previous query but have the result set include all the employees but department names for only the employees who were born before 1980.
select e.EMP_ID, e.L_NAME, e.DEP_ID, d.DEP_NAME 
from EMPLOYEES as e left outer join DEPARTMENTS as d 
on e.DEP_ID = d.DEPT_ID_DEP
AND year(e.B_DATE) < 1980;

--***use AND in the left outer join clause to get the right table's value where the condition matches

--Perform a Full Join on the EMPLOYEES and DEPARTMENT tables and select the First name, Last name and Department name of all employees.
SELECT e.F_NAME, e.L_NAME, d.DEP_NAME 
from EMPLOYEES as e full join DEPARTMENTS as d 
on e.DEP_ID = d.DEPT_ID_DEP;

--Re-write the previous query but have the result set include all employee names but department id and department names only for male employees.
SELECT e.F_NAME, e.L_NAME, d.DEP_NAME 
from EMPLOYEES as e full join DEPARTMENTS as d 
on e.DEP_ID = d.DEPT_ID_DEP
AND e.SEX = 'm';