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.
- Retrieve all the purchase details who bought Samsung brand phones.
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.
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.
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.
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.
- Retrieve all the purchase details who didn’t buy Samsung brand phones.
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.
- 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.
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.
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.
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.