SubQueries and Compound Statements

Sub-Queries

Sub Query is a technique that can be used to pass the value from one Query to another. These are usually not recommended by Database Administrators, as the Sub Queries are usually more than one query within a single query, this doesn’t allow the database to optimize the entire query as there are many involved.

SELECT * FROM account WHERE email='email@domain.com' WHERE account_id = (SELECT id from account WHERE email = 'xyz@domain.com);

Transclude of subquery.excalidraw

Using sub-queries is slower than using two normal queries, as it doesn’t allow the database to perform all the performance optimizations to allow for minimal time retrieval, sometimes using sub queries you might not always be able to use all the nice things that the database offers.

Compound Statements

There are statements which do more than one things in one statement for efficiency and Transactions

INSERT INTO fav(
post_id, account, howmuch
)
VALUES( 1,1,1,)
RETURNING *;

In the above query the keyword RETURNING means that after the operation return all the rows that have been affected, in this manner we do not have to run another query to fetch the updated data.