Aim: We will learn how to join tables and how to retrieve data from multiple tables. When data from more than one table in the database are needed, the two tables are joined together. Rows are joined together using common values, most of the time these are primary and foreign keys that provide a link between the two tables. The syntax of a simple join condition is: SELECT table1.column1, table1.column2,..., table2.column1, table2.column2 FROM table1, table2 WHERE table1.column1=table2.column2 **Please note that you may actually use as many tables as you need. You are not limited to use only two tables. Example: SELECT * FROM DEPARTMENTS; SELECT * FROM EMPLOYEES; Now join departments and employees tables. SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID; NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian Product joins all rows of the first table with all rows of the second table. That is if the first table has n rows and the second table has m rows, the output will have n*m rows. The following query will produce a Cartesian Product: SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS; **When table names are long, qualifying column names might be very time consuming. Usually people prefer to use table aliases for this purpose. Example: List the name of all employees who work for 'Purchasing' department SELECT E.FIRST_NAME "EMPLOYEE NAME" FROM EMPLOYEES E, DEPARTMENT D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND UPPER(D.DEPARTMENT_NAME) = 'PURCHASING'; **Table joins can be performed on columns that are not primary or foreign keys as well. SELECT E.FIRST_NAME FROM EMPLOYEES A, EMPLOYEES B WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID;     **All these joins are called equijoin because they use an equality to form the join. It is also possible to form nonequijoins. These are joins that use an operator (+) than other. Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed by placing a (+) sign on the right hand side of one of the attribute of join condition. The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other table. In other words (+) will allow NULL rows on the side that is placed. Syntax of an outer join: SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22 FROM table1, table2 WHERE table1.column11(+) = table2.column21(+); **Please be careful, you can use only one (+) in the join condition. Outer Examples: Example1: Display last name of all employees with their department information (department id and department name) including those employees who does not work for any department. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+); Example2: Display last name of all employees with their department information (department id and department name) including those departments that they do not have any employee. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id; SELF JOIN Joining a Table to Itself: Sometimes you need to join a able to itself. To find the name of each employee's manager, you need to join the EMPLOYEES table to itself. Example:Find the name of the managers for all employees. SELECT e.last_name,m.last_name FROM employees e, employees m WHERE .manager_id=m.employee_id; SQL> desc locations Name Null? Type ----------------------------------------- -------- ---------------------------- LOCATION_ID NOT NULL NUMBER(6) DEPARTMENT_ID NOT NULL NUMBER(6) ADDRESS VARCHAR2(25) CITY NOT NULL VARCHAR2(25) STATE NOT NULL VARCHAR2(25) COUNTRY VARCHAR2(20)     EXERCISES PART A: 1-Display the addresses of all the departments. Show locationID, street address,city,state and country name in the output. (Use Locations table) 2-Display the last name, department number and deparment name for all employees. 3-Display the last name, job, department number, and department name for all employees work in Toronto. (Employees,Departments,Locations) 4-Display employees' last name and employee number along with their manager's last name and manager number. Label the columns Employee, Emp#, Manager Mgr#, respectively. 5-Display the last name, department number,department name and region name for all employees who work in Europe. 6-What is the name of the manager for employee WINSTON (first name). 7-Display last name of employees whose manager is KING. 8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and Jobs) 9-Display all employees including King, who has no manager. Order the results by the employee number. 10-Display the name and hire date of any employee hired after employee Davis

Aim: We will learn how to join tables and how to retrieve data from multiple tables.
When data from more than one table in the database are needed, the two tables are joined together. Rows are joined
together using common values, most of the time these are primary and foreign keys that provide a link between the
two tables.
The syntax of a simple join condition is:
SELECT table1.column1, table1.column2,..., table2.column1, table2.column2
FROM table1, table2
WHERE table1.column1=table2.column2
**Please note that you may actually use as many tables as you need. You are not limited to use only two
tables.
Example:
SELECT *
FROM DEPARTMENTS;
SELECT *
FROM EMPLOYEES;
Now join departments and employees tables.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;
NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian
Product joins all rows of the first table with all rows of the second table.
That is if the first table has n rows and the second table has m rows, the output will have n*m rows.
The following query will produce a Cartesian Product:
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS;
**When table names are long, qualifying column names might be very time consuming. Usually people
prefer to use table aliases for this purpose.
Example: List the name of all employees who work for 'Purchasing' department
SELECT E.FIRST_NAME "EMPLOYEE NAME"
FROM EMPLOYEES E, DEPARTMENT D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
UPPER(D.DEPARTMENT_NAME) = 'PURCHASING';
**Table joins can be performed on columns that are not primary or foreign keys as well.
SELECT E.FIRST_NAME
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID;

 

**All these joins are called equijoin because they use an equality to form the join. It is also possible to form
nonequijoins. These are joins that use an operator (+) than other.
Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a
table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed
by placing a (+) sign on the right hand side of one of the attribute of join condition.
The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other
table. In other words (+) will allow NULL rows on the side that is placed.
Syntax of an outer join:
SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22
FROM table1, table2
WHERE table1.column11(+) = table2.column21(+);
**Please be careful, you can use only one (+) in the join condition.
Outer Examples:
Example1: Display last name of all employees with their department information (department id and department
name) including those employees who does not work for any department.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+);
Example2: Display last name of all employees with their department information (department id and
department name) including those departments that they do not have any employee.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id;
SELF JOIN
Joining a Table to Itself:
Sometimes you need to join a able to itself.
To find the name of each employee's manager, you need to join the EMPLOYEES table to itself.
Example:Find the name of the managers for all employees.
SELECT e.last_name,m.last_name
FROM employees e, employees m
WHERE .manager_id=m.employee_id;
SQL> desc locations Name Null? Type
----------------------------------------- --------
----------------------------
LOCATION_ID NOT NULL NUMBER(6)
DEPARTMENT_ID NOT NULL NUMBER(6)
ADDRESS VARCHAR2(25)
CITY NOT NULL VARCHAR2(25)
STATE NOT NULL VARCHAR2(25)
COUNTRY VARCHAR2(20)

 

EXERCISES
PART A:
1-Display the addresses of all the departments. Show locationID, street address,city,state and country name in the
output. (Use Locations table)
2-Display the last name, department number and deparment name for all employees.
3-Display the last name, job, department number, and department name for all employees work in
Toronto. (Employees,Departments,Locations)
4-Display employees' last name and employee number along with their manager's last name and manager number.
Label the columns Employee, Emp#, Manager Mgr#, respectively.
5-Display the last name, department number,department name and region name for all employees who work in
Europe.
6-What is the name of the manager for employee WINSTON (first name).
7-Display last name of employees whose manager is KING.
8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and
Jobs)
9-Display all employees including King, who has no manager. Order the results by the employee number.
10-Display the name and hire date of any employee hired after employee Davis