Within SQL users can use the result of one query within another query (query-ception), this can also be described as a nested or inner query. This is similar to using a Level of Detail (LOD) calculation.
This is useful for more complex queries that may rely on the condition of another query.
Sub-queries require brackets and use the WHERE, HAVING, or FROM functions.
An example of a use-case of using a subquery is shown below:
(SELECT MAX(Sales) FROM table)
This would be the equivalent of using this LOD in Tableau:
{ FIXED : MAX(Sales)}
Another example is where the user wants a list of orders and returns, as well as the all-time biggest sale.
SELECT
Orders,
Returns,
(SELECT MAX(Sales) FROM Sales_table)FROM Orders_Table;
Within SQL users can create CTEs (Common Table Expressions) which act as a virtual table that is only temporarily used as a reference for a query. CTEs require the WITH function, and acts similar to how subqueries are used.
To write a CTE within SQL use this format:
WITH cte_name (column 1, column 2, column...) AS (the subquery to define the CTE)
The cte_name is the alias given to the CTE you create, and is what is used to refer back to this table within the main query.
Within the brackets, are the column headers for the CTE you are creating.
After the AS, within the brackets, this is what defines what is within the CTE.
Once the CTE is defined you can mention it in your query as you would a normal table.
Sticking to the sales example, if the user wants to create an accessories products table they can use a CTE to select the columns they need.
WITH accessories_products AS ( SELECT product_id, SUM(sale_amount) as total_sales FROM sales WHERE category= "accessories" GROUPBY product_id ) SELECT*FROM product_sales;
Remember the CTE will only exist for the query, and will not save as a table in the database.