PostgreSQL - Sub Queries

 A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause.

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 can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.

There are a few rules that subqueries must follow −

  • Subqueries must be enclosed within parentheses.
  • A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
  • An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
  • Subqueries that return more than one row can only be used with multiple value operators, such as the IN, EXISTS, NOT IN, ANY/SOME, ALL operator.
  • The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.
Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])


Example

Consider the COMPANY table having the following records −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now, let us check the following sub-query with SELECT statement −

testdb=# SELECT *
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

This would produce the following result −

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)


Subqueries with the INSERT Statement

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.

The basic syntax is as follows −

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]


Example

Consider a table COMPANY_BKP, with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. Now, to copy complete COMPANY table into COMPANY_BKP, following is the syntax −

testdb=# INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;


Subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

The basic syntax is as follows −

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]


Example

Assuming, we have COMPANY_BKP table available, which is backup of the COMPANY table.

The following example updates SALARY by 0.50 times in the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

testdb=# UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

This would affect two rows and finally the COMPANY table would have the following records −

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)


Subqueries with the DELETE Statement

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

The basic syntax is as follows −

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]


Example

Assuming, we have COMPANY_BKP table available, which is a backup of the COMPANY table.

The following example deletes records from the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

testdb=# DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE > 27 );

This would affect two rows and finally the COMPANY table would have the following records −

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows)

Comments

Post a Comment

Popular posts from this blog

Java : Variables Declaring

[1]. Java Variables Variables are containers for storing data values. In Java, there are different types of variables, for example: String - stores text, such as "Hello". String values are surrounded by double quotes int - stores integers (whole numbers), without decimals, such as 123 or -123 float - stores floating point numbers, with decimals, such as 19.99 or -19.99 char - stores single characters, such as 'a' or 'B'. Char values are surrounded by single quotes boolean - stores values with two states: true or false [2]. Declaring (Creating) Variables To create a variable, you must specify the type and assign it a value: Syntax type variable = value ; Where type is one of Java's types (such as int or String), and variable is the name of the variable (such as x or name). The equal sign is used to assign values to the variable. To create a variable that should store text, look at the following example: Example Create a variable called  name  of type  Stri
Read more

LINUX Move and copy files using SSH

Often you will need to move one or more files/folders or copy them to a different location. You can do so easily using an SSH connection. The commands which you would need to use are mv (short from move) and cp (short from copy). The mv command syntax looks like this: mv configuration.php-dist configuration.php By issuing the above command we will move (rename) the file configuration.php-dist to configuration.php. You can also use mv to move a whole directory and its content: mv includes/* ./ This will move all files (and folders) in the includes/ directory to the current working directory. In some cases however, we will need to only update the files and move only files that were changed, which we can do by passing ‘ -u ’ as argument to the command: mv -u includes/* admin/includes The copy (cp) command works the same way as mv, but instead of moving the files/folders it copies them. For example: cp configuration.php-dist configuration.php The command will copy the co
Read more

SQL Self JOIN

SQL Self JOIN A self JOIN is a regular join, but the table is joined with itself. Self JOIN Syntax SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; T1 and T2 are different table aliases for the same table.
Read more