SQL Querying Techniques
- SQL (Structured Query Language) offers several advanced SQL querying techniques that allow you to effectively work with and manipulate databases in a more agile way.
- Pivot and Unpivot, SQL Subqueries, CTE (Common Table Expressions), and Dynamic SQL are some of the most useful SQL querying techniques one may use.
- These SQL querying techniques are tools of choice in areas such as mobile usability, social and demographic sources, and algorithms of recommendation.
- Let’s start with the first part by digging into each of these SQL querying techniques in a more intense manner.
1. Pivot and Unpivot in SQL
- Pivot and Unpivot are operations that modify data between rows and columns, and sometimes such options like pivoted or unpivoted data might give us a more visually effective and organized way of data representation.
Pivot:
- The PIVOT operation allows you to flip the data, where the unique values are transformed from one column to multiple columns in the resultant set.
- This is usually used when you want to summarize the data across different dimensions, such as converting time periods into columns or grouping data into a matrix.
Syntax:
SELECT <columns>
FROM
(
SELECT <columns>, <value_column>
FROM <table_name>
) AS source_table
PIVOT
(
<aggregation_function>(<value_column>)
FOR <pivot_column> IN (<list_of_values>)
) AS pivot_table;
- Assuming, for instance, that you are supplied with sales data with columns Product, Month, and SalesAmount, and you aim to organize the data to show each month as a column, the PIVOT operation can be used:
SELECT Product, [Jan], [Feb], [Mar]
FROM
(
SELECT Product, Month, SalesAmount
FROM Sales
) AS source_table
PIVOT
(
SUM(SalesAmount)
FOR Month IN ([Jan], [Feb], [Mar])
) AS pivot_table;
- This query will calculate the total amount of sales of each product for the periods of January, February, and March as separate columns.
Unpivot:
- UNPIVOT is the opposite of PIVOT.
- It turns the rows into columns, as is vital if you have to flatten or denormalize the data, for instance.
Syntax:
SELECT <columns>, <unpivoted_column>
FROM
(
SELECT <columns_to_unpivot>
FROM <table_name>
) AS source_table
UNPIVOT
(
<unpivoted_column>
FOR <column_name> IN (<columns_to_unpivot>)
) AS unpivot_table;
- For example, in the case of how the PIVOT operation generates a table with one column for each month of the year, below is the SQL script to change these months into rows with their corresponding sales amount:
SELECT Product, Month, SalesAmount
FROM
(
SELECT Product, [Jan], [Feb], [Mar]
FROM Sales_Pivoted
) AS source_table
UNPIVOT
(
SalesAmount FOR Month IN ([Jan], [Feb], [Mar])
) AS unpivot_table;
- This will produce those rows that have months as a column and their corresponding sales amount as the other column.
2. SQL Subquery
- A sub-query refers to a nesting of queries where within one query we have another one.
- Sub-queries are used most often in the retrieval of one single value, a set of values, or a table. Moreover, this data is to be in turn used by the outer query.
Types of Subqueries:
- Scalar Subquery: Returns a single value.
- Multi-row Subquery: Returns multiple rows, usually found next to the IN, ANY, or ALL.
- Correlated Subquery: This refers to columns from the outer inquiry that is initiated anew to the outer inquiry and thus is done for each row through the outer inquiry.
Syntax:
SELECT column_name
FROM table_name
WHERE column_name =(SELECT column_name FROM table_name WHERE condition);
- For instance, through employing a subquery, you can find employees who make more than the average salary.
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
- A correlated subquery is one where the inner query references a column from the outer query. For instance:
SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
3. CTE (Common Table Expressions)
- A CTE (Common Table Expression) is a set of temporary results of a query that can be referred in SELECT, INSERT, UPDATE, or DELETE statements.
- CTEs not only bring a lot of readability and modularity to complex queries, but they also make it possible to simplify hierarchical or recursive queries.
- For the same information to recur in different views, the common table expressions (CTEs) offered by SQL can be used to this purpose, similarly to views.
Syntax:
WITH cte_name AS
(
SELECT <columns>
FROM <table_name>
WHERE <condition>
)
SELECT <columns>
FROM cte_name;
- Example: Suppose you want to get the employees and their managers using a CTE.
WITH Employee_Manager AS
(
SELECT employee_id, manager_id
FROM employees
)
SELECT e.employee_id, e.manager_id, m.employee_id AS manager_employee_id
FROM Employee_Manager e
JOIN employees m ON e.manager_id = m.employee_id;
Recursive CTE:
- A recursive CTE is useful for hierarchical data, like organizational charts or bill-of-materials structures.
- It consists of two parts: the anchor member (base query) and the recursive member (which refers to itself).
WITH RECURSIVE CTE_name AS
(
SELECT <columns> FROM <table> WHERE <condition>
UNION ALL
SELECT <columns> FROM <table> JOIN CTE_name ON <recursive_condition>
)
SELECT * FROM CTE_name;
4. Dynamic SQL
- Dynamic SQL is SQL code that is produced and executed at run-time, notably when you do not have any advanced knowledge of the query structure.
- It is mainly used for such tasks as the construction of a query that is based on the dynamic data coming from the user (or any other entity), table names, or column names which may change.
Benefits of Dynamic SQL:
- It offers freedom to such a level that a person can execute queries with the parts that are selectable.
- Allows development of complex queries through programming.
- Convenient in pivot table related problems or handling table structures dynamically.
Syntax in SQL Server:
DECLARE @sql_query NVARCHAR(MAX)
SET @sql_query = 'SELECT * FROM ' + @table_name + ' WHERE ' + @condition
EXEC sp_executesql @sql_query;
- For instance, to build a query dynamically based on end user input for table and column objects, you should follow the syntax such as:
DECLARE @table NVARCHAR(100) = 'employees';
DECLARE @column NVARCHAR(100) = 'salary';
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT ' + @column + ' FROM ' + @table
EXEC sp_executesql @sql;
- Dynamic SQL is one approach to code for pivot operations when the column names shift accordingly.
Caution:
- SQL Injection: Using dynamic SQL is a critical security requirement. Take care to prevent SQL injection attacks and secure your system from security breaches by always sanitizing user input.
- Performance: Dynamic SQL queries are not compiled until runtime. As a result of this delay in the execution plan, performance of the system may suffer.