PostgreSQL – SQL Optimization
PostgreSQL is the most advanced general-purpose open source database in the world. pgAdmin is the most popular management tool or development platform for PostgreSQL. It is also an open source development platform. It can be used in any Operating Systems and can be run either as a desktop application or as a web in your browser. You can download the pgAdmin management tool from here.
We need optimization in SQL queries to get accurate and faster results in a more structured way. This will increase the performance and speedup of the process.
In this article we are going to discuss various SQL Optimization techniques in PostgreSQL. There are basically two types of optimization queries :
1. Inclusion
- IN clause : IN operator is mostly used with WHERE Clause to test for a given expression or a record it matches with a particular value from a set of values. It works like a multiple OR operator.
- ANY clause : It is used for comparison which returns Boolean TRUE value if the condition provided in the subquery is obtained for any values. If the condition is true for any of the values in that range then the output is TRUE.
- EXISTS clause : EXISTS operator mainly used in nested query (query inside a query). It is used to check the existence of the record provided in the subquery. It returns Boolean TRUE if one or more records match else FALSE when no rows match.
- INNER JOIN : Values matched in both tables are returned.
2. Exclusion
- NOT IN : It is just the negation of the IN clause which helps to execute the rows which don’t match the set of values.
- ALL : It is also used for comparison which returns Boolean TRUE value if the condition provided in the subquery is obtained for all the values. If the condition is true for all the values in that range then the output is TRUE.
- NOT EXISTS : It is just the negation of EXISTS clause.
- LEFT JOIN AND IS NULL : It is used to find all the rows which have NULL entries after performing LEFT JOIN operation on two tables.
Let’s implement the above SQL optimization techniques using a suitable example. Consider the sample tables shown below of an E-Commerce database.
Purchase Information |
Product ID |
Mobile Brand |
Price |
Customer Name |
1 |
OnePlus Nord 5G |
30000 |
Rishabh |
2 |
Samsung Galaxy M51 |
28000 |
Srishti |
3 |
iPhone 12 Pro |
128000 |
Aman |
4 |
Samsung Galaxy S20 |
55000 |
Harsh |
5 |
RealMe X50 Pro |
40000 |
Manjari |
6 |
Mi 10i 5G |
24000 |
Satadru |
Customer Information |
Customer ID |
Customer Name |
Email Address |
1 |
Srishti |
abc@gmail.com |
2 |
Rajdeep |
def@gmail.com |
3 |
Aman |
xxx@gmail.com |
4 |
Pooja |
xyz@gmail.com |
5 |
Tanmoy |
tdq@gmail.com |
BASIC SQL QUERY :
1. Creating a Database
CREATE DATABASE database_name
2. Creating a Table
CREATE TABLE Table_name(
col_1 TYPE col_1_constraint,
col_2 TYPE col_2 constraint
.....
)
col: Column name
TYPE: Data type whether an integer, variable character, etc
col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc
3. Inserting into a Table
INSERT INTO Table_name
VALUES(val_1, val_2, val_3, ..........)
val: Values in particular column
4. View The Table
SELECT * FROM Table_name
INCLUSION QUERIES :
1. IN CLAUSE :
SELECT col_name(s)
FROM Table1
WHERE value IN
(SELECT col_name(s) FROM Table2
WHERE condition)
col_name : Name of the column
SELECT col_name(s)
FROM Table_Name
WHERE col_name IN (val1,val2,val3,...)
col_name : Name of the column
val1 : Values in the column
- Retrieve all the purchase details who bought mobile phones and the price is more than thirty thousand.
IN CLAUSE
- Retrieve all the purchase details who bought Samsung brand phones.
IN CLAUSE
2. ANY CLAUSE
SELECT col_name(s)
FROM Table_Name
WHERE col_name rln_opr ANY
(SELECT col_name
FROM Table_Name
WHERE condition)
rln_opr: Relational Operator like (>,>=,<,<=,!= or <>,=)
- Retrieve all those customers information from the Customer Information Table who purchased phones and whose price is more than thirty thousand.
ANY CLAUSE
3. EXISTS CLAUSE
SELECT col_name(s)
FROM Table_Name WHERE EXISTS
(SELECT col_name(s)
FROM Table_Name
WHERE condition)
- Retrieve all those customers who bought mobile phones and their details exist in the customer information table.
EXISTS CLAUSE
4. INNER JOIN
SELECT * FROM Table1
INNER JOIN Table2
ON Table1.column_match=Table2.column_match;
Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
INNER JOIN OUTPUT
EXCLUSION QUERIES :
1. NOT IN
SELECT col_name(s)
FROM Table1
WHERE value NOT IN
(SELECT col_name(s) FROM Table2
WHERE condition)
SELECT col_name(s)
FROM Table_Name
WHERE col_name NOT IN (val1,val2,val3,...)
- Retrieve all the purchase details who bought mobile phones whose price is not more than thirty thousand.
NOT IN CLAUSE
- Retrieve all the purchase details who didn’t buy Samsung brand phones.
NOT IN CLAUSE
2. ALL
SELECT col_name(s)
FROM Table_Name
WHERE col_name rln_opr ALL
(SELECT col_name
FROM Table_Name
WHERE condition)
rln_opr: Relational Operator like (>,>=,<,<=,!= or <>,=)
- Retrieve the purchase information for those who purchased phones having prices not less than fifty thousand.
ALL OUTPUT
- Retrieve all those purchase details of the mobile phones whose price is greater than and less than the average price of all the mobiles sold.
Average Price
Greater than ALL OUTPUT
Less than ALL Output
3. NOT EXISTS
SELECT col_name(s)
FROM Table_Name WHERE NOT EXISTS
(SELECT col_name(s)
FROM Table_Name
WHERE condition)
- Retrieve all those customers whose information is present in the Customer Table but didn’t purchase any phone.
NOT EXISTS OUTPUT
4. LEFT JOIN AND IS NULL
SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.column_match=Table2.column_match
WHERE Table2.col_name IS NULL
Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
LEFT JOIN AND IS NULLL
Similarly, we can do it for all the other JOINS in SQL. It is generally used with WHERE Clause to get the rows that have NULL entry after joining two or more tables.
Last Updated :
07 Oct, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...