Sometimes you need to display data from multiple tables. In order to perform that you need to link one table to another table.
There are various ways in which we can obtain data from Multiple tables.I would be giving details on Joins, Set and Subqueries
A Join is a query that combines rows from two or more tables.
Oracle performs a join whenever multiple tables appear in the FROM clause. In order to join two tables, you need to identify the common columns that relate the two tables. In the WHERE clause you define the relationship between the tables listed in the FROM clause.
The most common operator used to relate two tables is the equality operator (=). This is called an equality join or equijoin. This simple join also known as inner join
1) when writing the select statement that joins tables, it is good practice to precede the column name with table name for clarity purpose.
2) To join m tables, we need at least m-1 conditions
3) We can use table alias for the tables name if the table name is lengthy. Table looks the code shorter and hence less memory
4) If you are not using table_name or tables alias while selecting the column and If multiple tables are having same column name, then we should specify the table_name for the column which is common across the tables
Select tab1.col,tab2.col from tab1 ,tab2 where tab1.col=tab2.col
SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME FROM EMP , DEPT where EMP. DEPT_ID= DEPT. DEPT_ID;
To execute a join of three or more table, Oracle joins two tables based on the join condition, then joins the result to another table, based on join conditions and repeats until all tables are joined.
Complex Joins – Have one or more conditions added in the WHERE clause. For example, if you are interested in all department and their locations outside the UK use
SELECT LOCATIONS.LOCATION_ID, CITY, DEPARTMENT_NAME FROM LOCATIONS, DEPARTMENTS WHERE LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID AND COUNTRY_ID != ‘UK’;
A non-equijoin is the join condition other than equality operator.
It is an inner join statement that uses an unequal operation (i.e.: <>, >, <, =, BETWEEN, etc.) to match rows from different tables
|SELECT e.ename, e.sal, s.grade
FROM emp e, sal_grade s
WHERE e.sal BETWEEN s.low_sal AND s.high_sal;
Cartesian Products or Cartesian Join
-A Cartesian join (or Cartesian product) occurs when data is selected from two or more tables and join condition is defined or no common relationship is defined in the WHERE clause
-If a join is performed and no join condition is specified, a Cartesian product results. A Cartesian product is a result set that is the product of the two tables’ total rows. If table “P” has 100 rows and table “Q” has 100 rows and a Cartesian product is developed, the resulting result set will have 10000 rows. If a query joins three or more tables, the optimizer may find a way to choose a join order that precludes a Cartesian product, but don’t count on it.
|SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME FROM EMP , DEPT ;|
The inner joins we have seen, return the matching rows from two or more tables according the join condition. The Outer Join mechanism returns the data from one table even if there is no corresponding row in the joining table. In Oracle syntax, enter a (+) sign next to the column name of the table where there may not be a corresponding row.
The two operators that can be used in an outer join condition are ‘=’ and ‘AND’.
|If you want to list all emp and dept info even if there is no data corresponding from the dept table then
select empno,ename,job,dname,loc from emp e,dept d where
The natural join specifies that the join relate all columns with the same names on the two tables.
Basically it compares the common columns of both tables with each other. One should check whether common columns exist in both tables before doing a natural join.
SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME FROM EMP NATURAL JOIN DEPT;
Here dept_id is the same column between the tables emp and dept
Natural join can join more than two tables
The cross join produces the cross-product of the two tables
|SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME FROM EMP CROSS JOIN DEPT ;|
Outer joins in ANSI SQL
They are represented by LEFT OUTER JOIN and RIGHT OUTER JOIN.
Left Outer Join – returns the rows matching from both tables as well as the unmatched rows from the table on the left of the join clause.
select empno,ename,job,dname,loc from emp e left outer join dept d on e.deptno=d.deptno;
Left outer join is indicated when the plus sign is on the right side of the equation.
Right Outer Join – returns the rows matching from both tables as well as the unmatched rows from the table on the right of the join clause.
select empno,ename,job,dname,loc from emp e right outer join dept d on e.deptno=d.deptno;
RIGHT OUTER JOIN is indicated when the plus sign is on the left of the equation.
Full Outer Join
Using the ANSI SQL, a full outer join between two tables can be achieved
|SELECT E.EMP_ID, E.LAST_NAME, D.DEPT_NAME FROM EMP E FULL OUTER JOIN DEPTS D ON E.DEPT_ID = D.DEPT_ID;|
This query will return all rows from both tables. Using Oracle syntax using E.DEPT_ID (+) = D.DEPT_ID (+) is impossible because the (+) sign may reference only one table.
Using Set Operators
Set operators can be used to select data from multiple tables. They combine the results of two or more queries. When using the Set operators each column the SELECT clause must match the columns in the first SELECT.
There are 4 Set operators:
|UNION||It returns all unique rows returned from both queries|
|UNION ALL||It returns all rows including duplicates|
|INTERSECT||It returns only the rows returned from both queries|
|MINUS||It returns unique rows selected by first query but not the rows selected from second query|
|Union||SELECT FIRST_NAME FROM EMP_DATA WHERE DEPT_ID = 7865 UNION SELECT FIRST_NAME FROM EMP_DATA WHERE ENAME LIKE ‘JAT%’;
This query will return unique rows of all employees that work in department 7865 and all employees who’s name begins with JAT
|Union all||SELECT FIRST_NAMEFROM EMP WHERE DEPT_ID = 8876 UNION ALL SELECT FIRST_NAMEFROM EMP WHERE ENAME LIKE ‘JAT%’;
This query will return all employees that work in department 8876 and all employees who’s name begins with JAT.
|INTERSECT||SELECT FIRST_NAME FROM EMP WHERE DEPT_ID = 900 INTERSECT SELECT FIRST_NAME FROM EMP WHERE ENAME LIKE ‘JAT%’;
This query will return all employees that work in department 900 who’s name begins with JAT
|MINUS||SELECT FIRST_NAMEFROM EMP WHERE DEPT_ID = 900 MINUS SELECT FIRST_NAMEFROM EMP WHERE ENAME LIKE ‘JAT%’;
This query will return the employees that work in department 900 but not employees who’s name begins with JAT.
A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is a query within a query
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
Subqueries answer the queries that have multiple parts. The parent query answers a part and the sub query answers other part
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.
Using subqueries in a FROM clause is known as an inline view.
Using subqueries in the WHERE clause is called a nested subquery. Up to 255 nested queries are allowed.
1) We need to put sub queries in parenthesis alwayss
2) We need to place subqueries on the right side of the comparison operator
3) Use single row operator with single row subqueries and multiple row operator with multiple row subqueries
|SELECT col1, col2
WHERE col1 OPERATOR
Single Row Subquery
It returns only one row of results and uses a single row operator (most common is the equal operator (=)). The other operators are > ,< ,>= ,=<
Single row subqueries can select data from the same table or from another table
|SELECT ENAME FROM EMP WHERE SALARY = (SELECT MIN(SALARY) FROM EMP);
SELECT ENAME FROM EMP WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE DNAME = ‘Admission’);
Multiple Row Subquery
It returns several rows of results from the subquery, uses the IN operator. In the previous query, if there was more than one accounting department, the query would have failed. Example of returning more than one row in the subquery
The other operator which are used is any and all
|SELECT ENAME, DEPT_ID FROM EMP WHERE DEPT_ID IN (SELECT DEPT_ID FROM DEPT WHERE DNAME LIKE ‘TA%’);
A correlated subquery is a subquery that relies on columns from the parent query. A correlated subquery is evaluated for each row processed by the parent query. The parent statement can be a SELECT, UPDATE or DELETE.
|SELECT LAST_NAME,FIRST_NAME,SALARY FROM EMPLOYEES E1 WHERE SALARY = (SELECT MAX(SALARY)
FROM EMPLOYEES E2 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID);
It returns exactly one value from one row, used most often in the VALUES clause of an INSERT statement, in and ORDER BY or WHERE clause and in a SELECT clause:
|SELECT LAST_NAME, FIRST_NAME,
DEPARTMENT_ID, (SELECT MAX(SALARY)
FROM EMPLOYEES E2
WHERE E2.DEPARTMENT_ID = E1.DEPARTMENT_ID) HIGH_SAL
FROM EMPLOYEES E1
WHERE FIRST_NAMELIKE ‘J%’;
The sub query executes once for each execution of the master query. A single-row subquery can only be used with single-row operators
The query can have more than one column in the SELECT clause of a subquery or in UPDATE statements
WHERE (LOCATION_ID, COUNTRY_ID)
IN (SELECT LOCATION_ID, COUNTRY_ID
WHERE STATE_PROVINCE = ‘NEWYORK’);
Subqueries in other DML Statements
Subqueries can be used in UPDATE, DELETE and INSERT statements
SET SALARY = (SELECT SALARY FROM EMP
WHERE EMPLOYEE_ID = 112408)
WHERE EMPLOYEE_ID = 193711;
INSERT INTO DEPT (DEPT_ID, DEPT_NAME)
VALUES ((SELECT MAX(DEPT_ID)
FROM DEPT), ‘NEW DEPT’);
DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUST_BACK
WHERE AGE > 40 );
Hope you like the details on Joins, Set and Subqueries. Looking for your feedback on this