Multiple-Row Subqueries

• Return more than one row
• Use multiple-row comparison operators

Using the ANY Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';

Using the ALL Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';
Null Values in a Subquery :

SELECT emp.ename FROM emp emp WHERE emp.empno NOT IN (SELECT mgr.mgr FROM emp mgr);

• The SQL statement in the slide attempts to display all the emp who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows
• Here we have to use IS NOT NULL in the inner query

Single-Row Subqueries

• Return only one row
• Use single-row comparison operators

Executing Single-Row Subqueries :

SELECT ename, job, sal FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7698) AND sal > (SELECT sal FROM emp WHERE empno = 7900);

Using Group Functions in a Subquery :

SELECT ename, job, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

The HAVING Clause with Subqueries :

• The PostgreSQL server executes subqueries first
• The PostgreSQL server returns results into the HAVING clause of the main query

SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);

What Is Wrong with This Statement ?

SELECT empno, ename FROM emp WHERE sal = (SELECT MIN(sal) FROM emp GROUP BY deptno);

• ERROR : more than one row returned by a subquery used as an expression
• Single-row operator with multiple-row subquery
  

Using Subqueries to Solve Queries

• Who has a sal greater than Scott’s ?

• 

Subquery Syntax :

• SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

• The subquery (inner query) executes once before the main query (outer query)
• The result of the subquery is used by the main query

Using a Subquery :

• SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘Scott');

Guidelines for Using Subqueries :

1. Enclose subqueries in parentheses
2. Place subqueries on the right side of the comparison condition
3. The ORDER BY clause in the Subquery is not needed unless you are performing Top-N analysis
4. Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries

Types of Subqueries :

• Single-row subquery

• Multiple-row subquery

INNER Versus OUTER Joins

• In SQL the join of two tables returning only matched rows is called an inner join
• A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) tables is called a left (or right) outer join
• A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join

LEFT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

RIGHT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

FULL OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

Non-Equijoins

Retrieving Records with Non-Equijoins :

• SELECT e.ename, e.sal, j.gra FROM emp e JOIN job_grd j ON e.sal BETWEEN j.lowest_sal AND j.highest_sal;

Outer Joins :

Self-Joins Using the ON Clause

• SELECT e.ename as emp, m.ename as mgr FROM emp e JOIN emp m ON (e.mgr = m.empno);

• 

Applying Additional Conditions to a Join :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno) AND e.mgr = 7698 ;
  

Creating Joins with the ON Clause

• The join condition for the natural join is basically an equijoin of all columns with the same name
• Use the ON clause to specify arbitrary conditions or specify columns to join
• The join condition is separated from other search conditions
• The ON clause makes code easy to understand

Retrieving Records with the ON Clause :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno);

• 

Creating Joins with the USING Clause

• If several columns have the same names but the data types do not match, the NATURALJOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin
• Use the USING clause to match only one column when more than one column matches
• Do not use a table name or alias in the referenced columns
• The NATURAL JOIN and USING clauses are mutually exclusive

Joining Column Names :



Retrieving Records with the USING Clause :

• SELECT emp.empno, emp.ename, dept.loc, deptno FROM emp JOIN dept USIN (deptno) ;

Few Important Points reg Joins

Qualifying Ambiguous Column Names :

• Use table prefixes to qualify column names that are in multiple tables
• Use table prefixes to improve performance
• Use column aliases to distinguish columns that have identical names but reside in different tables
• Do not use aliases on columns that are identified in the USING clause and listed elsewhere in the SQL statement

Using Table Aliases :

• Use table aliases to simplify queries
• Use table aliases to improve performance

SELECT e.empno, e.ename, d.loc, deptno FROM emp e JOIN dept d USING (deptno) ;

Creating Natural Joins

• The NATURAL JOIN clause is based on all columns in the two tables that have the same name
• It selects rows from the two tables that have equal values in all matched columns
• If the columns having the same names have different data types, an error is returned

Retrieving Records with Natural Joins :

• SELECT empno, dname,loc FROM emp NATURAL JOIN dept ;

• 

Creating Joins with the USING Clause :

• If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin
• Use the USING clause to match only one column when more than one column matches
• Do not use a table name or alias in the referenced columns
• The NATURAL JOIN and USING clauses are mutually exclusive

Joining Column Names :



Retrieving Records with the USING Clause :

• SELECT emp.empno, emp.ename, dept.loc, deptno FROM emp JOIN dept USIN (deptno) ;

Displaying Data from Multiple Tables

Obtaining Data from Multiple Tables :



Types of Joins :

• Joins that are compliant with the SQL:1999 standard include the following :

1. Cross joins
2. Natural joins
3. USING clause
4. Full (or two-sided) outer joins
5. Arbitrary join conditions for outer joins

Joining Tables Using SQL : 1999 Syntax

• Use a join to query data from more than one table

SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];

Restricting Group Results

• In the same way that you use the WHERE clause to restrict the rows that you select, you use the HAVING clause to restrict groups

• To find the maximum salary in each of the departments that have a maximum salary greater than $10,000, you need to do the following :

1. Find the average salary for each department by groupingby department number
2. Restrict the groups to those departments with a maximum salary greater than $10,000

Restricting Group Results with the HAVING Clause :

• When you use the HAVING clause, the PostgreSQL server restricts groups as follows:

1. Rows are grouped
2. The group function is applied
3. Groups matching the HAVING clause are displayed

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
Example :

• SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>4000 ;
• SELECT job, SUM(sal) as PAYROLL FROM emp WHERE job NOT LIKE '%MAN%' GROUP BY job HAVING SUM(sal) > 4000 ORDER BY SUM(sal);

SELECT column, group_function FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Illegal Queries - Using Group Functions

• Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause

• SELECT deptno, COUNT(ename) FROM emp;
• ERROR : column "emp.deptno" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803
• Column missing in the GROUP BY clause

• You cannot use the WHERE clause to restrict groups
• You use the HAVING clause to restrict groups
• You cannot use group functions in the WHERE clause

• SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 8000 GROUP BY deptno;
• WHERE AVG(salary) > 8000
  ERROR : aggregates not allowed in WHERE clause
• Cannot use the WHERE clause to restrict groups
  

Creating Groups of Data

GROUP BY Clause Syntax :

• SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

• You can divide rows in a table into smaller groups by using the GROUP BY clause

Using the GROUP BY Clause :

• All columns in the SELECT list that are not in group functions must be in the GROUP BY
  clause
• SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ;

• The GROUP BY column does not have to be in the SELECT list
• SELECT AVG(sal) FROM emp GROUP BY deptno ;

Grouping by More Than One Column :

• You can also group by multiple columns
• Example : select deptno, job, avg(sal) from emp group by deptno, job;
  

Cross Joins

Cartesian Products :

• A Cartesian product is formed when:
  • A join condition is omitted
  • A join condition is invalid
  • All rows in the first table are joined to all rows in the second table

• To avoid a Cartesian product, always include a valid join condition

Creating Cross Joins :

• The CROSS JOIN clause produces the cross-product of two tables
• This is also called a Cartesian product between the two tables

• SELECT ename, dname FROM emp CROSS JOIN dept ;

• 

Using Group Functions - Avg, Sum, Min, Max and Count

Using the AVG and SUM Functions :

• You can use AVG and SUM for numeric data
• SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal) FROM emp WHERE job LIKE '%MAN%';

Using the MIN and MAX Functions :

• You can use MIN and MAX for numeric, character, and date data types
• SELECT MIN(hiredate), MAX(hiredate) FROM emp;

Using the COUNT Function :

• COUNT(*) returns the number of rows in a table
  
• SELECT COUNT(*) FROM emp WHERE deptno = 10;
• COUNT(expr) returns the number of rows with non-null values for the expr
• SELECT COUNT(comm) FROM emp WHERE deptno = 30;

Using the DISTINCT Keyword :

• COUNT(DISTINCT expr) returns the number of distinct non-null values of the expr
• To display the number of distinct department values in the EMP table
• SELECT COUNT(DISTINCT deptno) FROM emp;