B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Unit IV
Sorting and Grouping Data in SQL & Joining Tables &
Subqueries in Oracle
* ORDER BY clause –
The ORDER BY clause is used to sort the records in your result set. The ORDER BY
clause can only be used in SELECT statements.
Syntax –
SELECT column1, column2, …… FROM tbl_name
[WHERE condition(s)]
ORDER BY column1, column2, …… [ASC | DESC];
SELECT * FROM STUD;
RN NAME CITY
2 BALAJI NANDED
4 DIPAK UDGIR
1 AMIR LATUR
3 CHETAN PUNE
5 HEMANT LATUR
Example – 1) Show the details of the student according to the city’s name.
SQL> SELECT * FROM STUD ORDER BY CITY ASC;
RN NAME CITY
1 AMIR LATUR
5 HEMANT LATUR
2 BALAJI NANDED
3 CHETAN PUNE
4 DIPAK UDGIR
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
2) Show the details of the student according to the city’s name in descending
order.
SQL> SELECT * FROM STUD ORDER BY CITY DESC;
RN NAME CITY
4 DIPAK UDGIR
3 CHETAN PUNE
2 BALAJI NANDED
5 HEMANT LATUR
1 AMIR LATUR
Note: If the ASC or DESC modifier is not provided in the ORDER BY clause, the
results will be sorted by column in ascending order.
3) Show the details of the student according to the roll numbers.
SQL> SELECT * FROM STUD ORDER BY RN;
RN NAME CITY
1 AMIR LATUR
2 BALAJI NANDED
3 CHETAN PUNE
4 DIPAK UDGIR
5 HEMANT LATUR
* GROUP BY clause –
The GROUP BY clause is used in a SELECT statement to collect data across multiple
records and group the results by one or more columns.
Syntax –
SELECT column1, column2, ... , columnN
aggregate_function (aggregate_column)
FROM tbl_name
[WHERE condition(s)]
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
GROUP BY column1, column2, ... , columnN;
Example – Find out how many students are there in each city.
SQL> SELECT CITY, COUNT (RN) AS "NO. OF STUDENTS" FROM STUD GROUP BY CITY;
CITY NO. OF STUDENTS
LATUR 2
PUNE 1
UDGIR 1
NANDED 1
* HAVING clause –
The HAVING clause is used in combination with the GROUP BY clause to restrict
the groups of returned rows to only those whose the condition is TRUE.
Syntax –
The syntax for the HAVING clause in Oracle/PLSQL is:
SELECT column1, column2, ... , columnN
aggregate_function (aggregate_column)
FROM tbl_name
[WHERE condition(s)]
GROUP BY column1, column2, ... , columnN;
HAVING having_condition;
* having_condition – Only those groups whose condition evaluates to TRUE will be
included in the result set.
Example – 1) Find out the students having more than one student from city.
SQL>SELECT CITY, COUNT (RN) AS "NO. OF STUDENTS" FROM STUD
GROUP BY CITY HAVING COUNT (RN) > 1;
CITY NO. OF STUDENTS
LATUR 2
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
What is JOIN?
A join is a query that combines rows from two or more tables or views. Join clause
use when select records rows from two or more tables from the database. It’s depending
on certain columns from two tables. Matching columns are evaluate and if predicated
TRUE return a records set data in specified format.
SQL Join four different types: Inner Join, Outer Join, Self-Join, Cross Join.
Considering following type of Join visually.
* INNER JOIN –
Inner Join is the simplest and most common type of join
Image representation of Inner Join –
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Syntax –
SELECT column1, column2, …
FROM table1 INNER JOIN table2
ON table1.column = table2.column;
Example – List the supplier details along with order numbers to which they are belong
SUPPLIER_I SUPPLIER_PRODUC SUPPLIER_CIT ORDER_N SUPPLIER_I
D T Y O D
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
3 CARS MUMBAI 103 4
Table – SUPPLIERS Table – ORDERS
SQL>SELECT S.SUPPLIER_ID, S.SUPPLIER_PRODUCT, S.SUPPLIER_CITY, O.ORDER_NO
FROM SUPPLIERS S INNER JOIN ORDERS O
ON S.SUPPLIER_ID = O.SUPPLIER_ID;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO
1 SHOES LATUR 101
2 COMPUTERS PUNE 102
Above example will return all rows from "suppliers" and "orders" table where
there is a matching “supplier_id” value in both the “suppliers” and “orders” tables.
* EQUI JOIN –
Equi join is a specific type comparison base join (equally comparison) not allowing
other comparison operator such as <, > <= etc. And create record set results that are
combining columns value from the tables (two or more table).
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
THETA Style Syntax –
SELECT column1, column2, …
FROM table1, table2
WHERE table1.column_name = table2.column_name;
It uses a comparison operator in the WHERE clause to refer equality.
Example – The following examples return the first name and job of each employee and
the number and name of the department in which the employee works.
SUPPLIER_I SUPPLIER_PRODUC SUPPLIER_CIT ORDER_N SUPPLIER_I
D T Y O D
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
3 CARS MUMBAI 103 4
Table – SUPPLIERS Table – ORDERS
SQL>SELECT * FROM SUPPLIERS S, ORDERS O
WHERE S.SUPPLIER_ID = O.SUPPLIER_ID;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO SUPPLIER_ID
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
ANSI Style Syntax –
SELECT column1, column2, …
FROM table1 JOIN table2
ON table1.column_name = table2.column_name;
Equi join use JOIN keyword specify table name and ON keyword specify the join
predicate condition.
Example –
SQL>SELECT * FROM SUPPLIERS S JOIN ORDERS O
ON S.SUPPLIER_ID = O.SUPPLIER_ID;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO SUPPLIER_ID
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
USING Style Syntax –
SELECT column1, column2, …
FROM table1 JOIN table2
USING (common_column_name);
If join predicate condition both table column name are same, then you can write
this query shorthand way by using USING Keyword.
Example –
SQl>SELECT * FROM SUPPLIERS JOIN ORDERS
USING (SUPPLIER_ID);
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO
1 SHOES LATUR 101
2 COMPUTERS PUNE 102
* Natural join –
Natural join is a same as Equi join but different is resulting contains allow only one
column for each pair of same columns named. Record set contains haven't same name
columns are found.
Syntax –
SELECT column1, column2, …
FROM table1 NATURAL JOIN table2;
Natural join query use NATURAL JOIN keyword to specify table name.
Example –
SUPPLIER_I SUPPLIER_PRODUC SUPPLIER_CIT ORDER_N SUPPLIER_I
D T Y O D
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
3 CARS MUMBAI 103 4
Table – SUPPLIERS Table – ORDERS
SQL>SELECT * FROM SUPPLIERS NATURAL JOIN ORDERS;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO
1 SHOES LATUR 101
2 COMPUTERS PUNE 102
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
* OUTER JOIN –
An outer join is similar to equijoin but it gets also the non-matched rows from the
table. It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 9i
ANSI/ISO 1999 standard.
SUPPLIER_I SUPPLIER_PRODUC SUPPLIER_CIT ORDER_N SUPPLIER_I
D T Y O D
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
3 CARS MUMBAI 103 4
Table – SUPPLIERS Table – ORDERS
1) Left Outer Join –
Left Join (Left Outer Join) always contains all records of left table (Table 1) even of
join condition does not find any matching record in right table (Table 2).
Image representation of Left Outer Join –
Syntax –
SELECT column1, column2, …
FROM table1 LEFT OUTER JOIN table2
ON table1.column = table2.column;
Example – List the supplier details along with the order details (if any) using Left Outer
Join.
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
SQL>SELECT S.SUPPLIER_ID, S.SUPPLIER_PRODUCT, S.SUPPLIER_CITY, O.ORDER_NO,
O.SUPPLIER_ID FROM SUPPLIERS S LEFT OUTER JOIN ORDERS OON S.SUPPLIER_ID =
O.SUPPLIER_ID;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO SUPPLIER_ID
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
3 CARS MUMBAI
Above example would return all records from the left table i.e. “suppliers” and only
those records from the right table i.e. “orders” where the join fields are equal.
2) Right Outer Join –
Right Join (Right Outer Join) always contains all records of right table (Table 2) even
of join condition does not find any matching record in left table (Table 1).
Image representation of Right Outer Join –
Syntax –
SELECT column1, column2, …
FROM table1 RIGHT OUTER JOIN table2
ON table1.column = table2.column;
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Example – List the order details along with the supplier details (if any) using Right Outer
Join.
SQL>SELECT S.SUPPLIER_ID, S.SUPPLIER_PRODUCT, S.SUPPLIER_CITY, O.ORDER_NO,
O.SUPPLIER_ID
FROM SUPPLIERS S RIGHT OUTER JOIN ORDERS O
ON S.SUPPLIER_ID = O.SUPPLIER_ID;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO SUPPLIER_ID
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
103 4
Above example would return all rows from the right table i.e. “orders” table and
only those rows from the left table i.e. “suppliers” table where the join condition is met.
3) Full Outer Join –
Full Join (Full Outer Join) always contains all records of left table (Table 1) and right
table (Table 2) even of join condition does not find any matching record in both left or
right table. Returned result contains set NULL value for all column that are lack of value
in matching rows.
Image representation of Full Outer Join –
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Syntax –
SELECT column1, column2, …
FROM table1 FULL OUTER JOIN table2
ON table1.column = table2.column;
Example – This following query retrieves all rows in the SUPPLIERS table, even if there is
no match in the ORDERS table. It also retrieves all rows in the ORDERS table, even if there
is no match in the SUPPLIERS table.
SQL> SELECT S.SUPPLIER_ID, S.SUPPLIER_PRODUCT, S.SUPPLIER_CITY,
O.ORDER_NO, O.SUPPLIER_ID
FROM SUPPLIERS S FULL OUTER JOIN ORDERS O
ON S.SUPPLIER_ID = O.SUPPLIER_ID;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY ORDER_NO SUPPLIER_ID
1 SHOES LATUR 101 1
2 COMPUTERS PUNE 102 2
103 4
3 CARS MUMBAI
Above example will return all rows from the left table i.e. “suppliers” table and all
rows from the right table i.e. “orders” table and whenever the join condition is not met,
it places the NULL value.
* CROSS JOIN / CARTESIAN PRODUCT –
The CROSS JOIN specifies that all rows from first table join with all of the rows of
second table.
Syntax –
SELECT * FROM table1 CROSS JOIN table2;
Or
SELECT * FROM table1, table2;
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Image representation of Cross Join –
Example –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY CUST_NO CUST_NAME
1 SHOES LATUR C1 AMOL
2 COMPUTERS PUNE C2 RAVI
3 CARS MUMBAI
Table – CUSTOMERS
Table – SUPPLIERS
SQL>SELECT * FROM SUPPLIERS CROSS JOIN CUSTOMERS;
Or
SQL>SELECT * FROM SUPPLIERS, CUSTOMERS;
Output –
SUPPLIER_ID SUPPLIER_PRODUCT SUPPLIER_CITY CUST_NO CUST_NAME
1 SHOES LATUR C1 AMOL
2 COMPUTERS PUNE C1 AMOL
3 CARS MUMBAI C1 AMOL
1 SHOES LATUR C2 RAVI
2 COMPUTERS PUNE C2 RAVI
3 CARS MUMBAI C2 RAVI
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
* SELF JOIN –
Self-Join is a specific type of Join. In Self Join, a table is joined with itself. A self-join
simply specifies that each rows of a table is combined with itself and every other row of
the table.
Image representation of Cross Join –
Syntax –
SELECT a.column_name, b.column_name, ...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
Example – Display all customer details that live in the city where AMOL lives.
CUST_NO CUST_NAME CITY
C1 AMOL LATUR
C2 RAVI LATUR
C3 DIPAK PUNE
C4 ATUL MUMBAI
Table – CUSTOMERS
SQL>SELECT B.* FROM CUSTOMERS A, CUSTOMERS B
WHERE A.CUST_NAME = 'AMOL' AND A.CITY = B.CITY;
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Output –
CUST_NO CUST_NAME CITY
C1 AMOL LATUR
C2 RAVI LATUR
Above example we are use A and B table alias name for CUSTOMERS table.
Joining three Tables :-
•Consider the following tables to Join
1) Regions
Region_Id Region_Name
R101 East
R102 West
2) Countries
Country_Id Country_Name Region_Id
C101 India R101
C102 America R102
3) Locations
Location_Id City Country_Id
L101 Latur C101
L102 Pune C102
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Example
SQL> Select R.Region_NAME, C.Country_Name, L.City
from Regions R, Countries C, Locations L
WHERE R.Region_ID=C.Region_ID
AND
C.Country_ID=L.Country_ID;
Output:-
Region_Name Country_Name City
East India Latur
West America Pune
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
SQL Subquery: -
Subquery or Inner query or Nested query is a query in a query. SQL subquery is usually
added in the WHERE Clause of the SQL statement. Most of the time, a subquery is used
when you know how to search for a value using a SELECT statement, but do not know
the exact value in the database.
Subqueries are an alternate way of returning data from multiple tables.
Subqueries can be used with the following SQL statements along with the comparison
operators like =, <, >, >=, <= etc.
SELECT
INSERT
UPDATE
DELETE
SQL Subquery Example:
1) Usually, a subquery should return only one record, but sometimes it can also return
multiple records when used with operators LIKE IN, NOT IN in the where clause.
Example-
SQL> SELECT first_name, last_name, Games
FROM student_details
WHERE games NOT IN ('Cricket', 'Football');
Subquery output
first_name last_name Games
------------- ------------- ----------
Shekar Sharma Badminton
Priya Kotwal Chess
2) if you do not know their names, then to get their id's you need to write the query in
this manner,
SQL> SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Subquery Output:
id first_name
-------- -------------
100 Rahul
102 Stephen
Correlated Subquery
A query is called correlated subquery when both the inner query and the outer query
are interdependent.
Non-Correlated Subquery
2) If a subquery is not dependent on the outer query it is called a non-correlated
subquery
Prepared by, Mr. V. D. Patil
COCSIT, Latur
B Sc CS SY Unit IV-Sorting and Grouping data in SQL &Joining Tables&
Subqueries in Oracle
Prepared by, Mr. V. D. Patil
COCSIT, Latur
