[Syntax] Writing Efficient Query

1. Views

In a database, a View is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in relational database, a view doesn't form part of physical schema. A a result, it is a virtual table computed or collected dynamically from data in database when access to that view is requested.

 

1.1 CREATE

CREATE VIEW [db_name].[view_name] AS 
    SELECT ...
      FROM ...
       ...;

 

1.2 DROP

DROP VIEW [db_name].[view_name];

 

2. Subquery

Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function. A nested query is also known as subquery. While JOINs and other table operations provide computationally superior alternative in many cases, the use of subqueries introduces a hierarchy in execution that can be useful or necessary.

 

SELECT [col_name1], 
       (SELECT [col_name2] FROM [table_name2]) AS [new_col], 
       ...
  FROM [table_name1] 
 WHERE [col_name] > (SELECT FUNC([col_name3] FROM [table_name3]) 
   ...;

 

3. Common Table Expression(CTEs)

A Common Table Expression, or CTEs is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE clause. CTEs can be thought of as alternatives to subquery, views, and inline user-defined functions.

 

WITH [CTE_name1] AS 
     (
        SELECT ...
        FROM [table_1] 
        ...
     ),
     [CTE_name2] AS 
     (
         ...
     )
SELECT ...
  FROM [CTE_name1] 
   ...;

 

4. Others

4.1 Check if there are same values in two tables

The method of checking whether elements in columns other than primary key and foreign key are the same inside two tables is to caculate whether the set of differences in each table is NULL or not, and the AND operator shows that the elements in the rows of the two tables are same.

 

(
SELECT t.track_id 
  FROM track AS t 
 WHERE t.album_id = (SELECT t2.album_id
                     FROM track AS t2
                     WHERE t2.track_id = ifs.first_track)

EXCEPT 

SELECT il.track_id
  FROM invoice_line AS il 
 WHERE il.invoice_id = (SELECT il2.invoice_id
                        FROM invoice_line AS il2
                        WHERE il2.invoice_id = ifs.invoice_id)
) IS NULL

AND  

(
SELECT il.track_id
  FROM invoice_line AS il 
 WHERE il.invoice_id = (SELECT il2.invoice_id
                        FROM invoice_line AS il2
                        WHERE il2.invoice_id= ifs.invoice_id)

EXCEPT 

SELECT t.track_id 
  FROM track AS t 
 WHERE t.album_id = (SELECT t2.album_id
                     FROM track AS t2
                     WHERE t2.track_id = ifs.first_track)    
) IS NULL