An Interest In:
Web News this Week
- April 19, 2024
- April 18, 2024
- April 17, 2024
- April 16, 2024
- April 15, 2024
- April 14, 2024
- April 13, 2024
CTE in SQL
One thing I like about SQL are Common Table Expressions (CTE). CTE were introduced to simplify SQL queries.
A CTE allows for the creation of a temporary result set, which can be treated just like a view. You can perform JOIN operations on them and the results can be filtered. This allows you to separately define queries instead of writing nested queries. This makes performing operations on datasets easier, especially when working with complex operations. This also helps to make the code more readable.
The syntax is as follows:
Define the CTE name and columns.
WITH cteName (column1, column2) AS
Define the CTE query. Example with SELECT statement:
WITH cteName (column1, column2) AS ( SELECT 1, 2 )
Define the outer query, which will reference the CTE name.
WITH cteName (column1, column2) AS ( SELECT 1, 2 ) SELECT * FROM cteName
Here is an example of a sqlzoo problem solved with CTE instead of nested queries.
8. For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5
with b as ( SELECT * FROM Caller),a as ( SELECT Customer.Company_name, Customer.Contact_id, COUNT(*) AS nc FROM Customer JOIN Caller ON (Customer.Company_ref = Caller.Company_ref) JOIN Issue ON (Caller.Caller_id = Issue.Caller_id) GROUP BY Customer.Company_name, Customer.Contact_id HAVING COUNT(*) < 5)SELECT a.Company_name, b.first_name, b.last_name, a.nc from b join a ON (a.Contact_id = b.Caller_id);
Original Link: https://dev.to/stuxnat/cte-in-sql-2h0n
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To