Riley Lambert

Riley Lambert

1656318537

SQL Tutorial | Write a SQL Query to Fetch Data from a Database

SQL Tutorial | How to write a SQL Query

How to write a sql query to fetch data from a database. Part of a music player sql app tutorial.  Learn to create and use a database application.


An Ultimate Guide to Write an SQL Query

In this tutorial, we’ve collected all the essential information about writing SQL queries into one ultimate guide to help you gain and practice this knowledge more effectively.

A Brief Look at SQL and Its Main Components

Structured Query Language (SQL) is a programming language specific to a domain, designed to help maintain structured data held in a database. Basically, this language is what helps us communicate with a database, update it, and retrieve data from it.

From this definition, we can conclude that you can use SQL to perform a variety of tasks, like:

  • creating a database
  • launching tables and writing queries in a database
  • requesting information from a database
  • update or delete specific records or an entire database

To better understand how this language functions, you also need to have a basic knowledge of its main components or dialects.

  • Data Definition Language – the SQL dialect used to create a database, update the data inside of it, or delete the database entirely.
  • Data Manipulation Language – the SQL dialect used to maintain the database and specify what you want to do with it (enter, change, or extract).
  • Data Control Language – the SQL dialect used to secure the database.

Some experts also single out the Transaction Control Language dialect that is used to manage and control transactions in order to maintain data integrity (the assurance of accuracy and consistency) in the SQL statement.

What Do You Need to Write an SQL Query?

To write an SQL query, you will first need to create a relational database.

In SQL, we call a database relational because it is divided into tables that are related to each other. These tables break down the data into smaller and more manageable units, which allows better maintenance and overall performance.

Where can you create an SQL database?

To do it, you need to have a Database Management System (DBMS) installed. A DBMS is a software that helps you create and manage the database. If you don’t have one installed on your system already, here are a few most popular options:

  • MySQL (open-source)
  • SQL Server Express (open-source)
  • Oracle (paid)

Regardless of the DBMS you choose, all of them provide security and protection for all the databases you create. It also helps maintain consistency if there are several users working on the DBMS at once.

The Guide to Writing an SQL Query

Now, let’s take a closer look at what you need to know to write an SQL query as well as some common mistakes to avoid.

Step #1: Understand the Process behind the SQL Query

Before you start writing an SQL query, you need to understand how it works, when performing commands.

When you enter a query, it is immediately parsed into a tree. A parser, which is a computer program that translates SQL statements into a parse tree, helps identify whether the query fits the syntactical as well as semantic requirements, i.e., that it is recognizable.

Then, if everything is correct, the parser creates an internal query, which is then passed to the rewrite engine.

After that, the task optimizer, which analyzes how many execution plans a query has, finds the optimal execution plan for your given query. This plan represents the algorithm, which will be used to perform the command.

If the query is written correctly, you will get the results you expect.

Now, let’s take a look at what it takes to write a proper SQL query.

Step #2: Get a Database Ready

So, as we already mentioned, to write an SQL query, you need to create an environment for it by launching a database.

If you already have your DBMS software installed, you can proceed right to creating a database by using a ‘CREATE DATABASE’ statement. The basic syntax of this command will look like this:

CREATE DATABASE   database_name;

For instance, if you want to create a database for client reviews, the result will look like this:

CREATE DATABASE client_reviews;

To move on to using your database, you need to continue your SQL query with the ‘USE’ statement:

USE client_reviews;

This will make this database a target for all your future commands.

Keep in mind: if the database with this name already exists, you will get an error message. However, to avoid this, you can use the ‘IF NOT EXISTS’ statement. Here’s how it looks like in MySQL:

mysql> CREATE DATABASE IF NO EXISTS client_reviews;

If your query is correct, you will get an OK message.

Step #3: Create a Table to Organize Information

If the database is ready, you can now proceed to creating a table. As we already mentioned, tables help you structure your queries and make the data more manageable.

To create a table, you need to input the ‘CREATE TABLE’ statement. The syntax for this statement will look similar to this:

CREATE TABLE table_name (
column1_name_constraint,
…
)

It is clear what we should insert in the table name and column name, but let’s take a closer look at the types of constraints.

A constraint is a declaration of data type, which indicates what kind of data the column will include. There are several kinds of constraints that you can include in a column:

  • CHAR – fixed-length data strings (max. size of 255 characters)
  • DATE – dates in the YYYY-MM-DD format
  • DATETIME – dates in the YYYY-MM-DD format and time in the HH:MM:SS format
  • DECIMAL – precise decimal data
  • INT – numbers within the range of 2147483648 to 2147483647
  • TEXT – textual data (max. size of 65,500 characters)
  • TIMESTAMP – timestamp data with the precise number of seconds
  • VARCHAR – variable-length data strings (max. size of 65,500 characters)
  • Modifiers – UNIQUE constraint ensures unique value for each row in a column; PRIMARY-KEY constraint marks a relevant field in the table; DEFAULT constraint represents the default value of the columns; AUTO_INCREMENT automatically assigns value to a field if it has not been specified; CHECK constraint puts a restriction on the values that can be included in one column; NOT NULL constraint excludes the null value from a field.

If we create a table for each of the people who have given their customer reviews, the final result will look like this:

CREATE TABLE customer profiles (
id INT NOT NULL AUTO_INCREMENT
name VARCHAR NOT NULL
birth_date DATE
review TEXT
) ;

When creating a table, you can encounter the same problem as with a database if the table does not exist. To avoid it, to the ‘CREATE TABLE’ statement, you should also add the ‘IF NOT EXISTS’ statement.

Step #4: Start with the Basic SQL Statements

When the table is ready and operational, you can now continue writing SQL queries by inserting data inside the table.

Here are all basic statements to input and manage the data inside the table:

1) ‘INSERT INTO’ statement

This statement is used to include new rows into a table. Here’s how the syntax will look like in this case:

INSERT INTO table_name (column1, column2, column 3);

You can also include values – the data that you want to include in each column.

Here’s how the final query would look like for our customer reviews table after inserting data into it:

INSERT INTO customer profiles (name, birth_date, review)
VALUES (‘Jennifer Dickinson’, ‘1987-07-07’, ‘good service’)

The final result will give you a numbered list of the names, birth dates, and reviews in the form of a table.

2) ‘SELECT’ clause

This clause is used to select and extract data from one or different tables. The syntax for this clause will follow this pattern:

SELECT column1_name, column2_name FROM table_name
-_name

The combination of the ‘SELECT’ and ‘FROM’ clauses allows you to fetch information from one or several columns within one or different tables. If you want to select all columns in one table, you can simply use the combination of ‘SELECT’, ‘FROM’ clauses, and the name of the table.

3) ‘WHERE’ clause

By inserting this clause in a table, you can select data based on a certain condition. It is helpful if you want to select for an update or entirely delete one of the records in a row or a column based on a condition like a name, birth date, etc. Here’s the basic syntax using this clause:

SELECT column1_name FROM table_name WHERE condition
-_name

As you can see, the WHERE clause is used together with the ‘SELECT’ and ‘FROM’ clauses to help you fetch the right data from the right column or row.

You can also use the ‘WHERE’ clause to filter different records based on a certain condition.

To make sure that you select the right data, this clause allows you to use several basic comparison operators:

  • = (equal)
  • > (greater than)
  • < (less than)
  • >= (greater than or equal)
  • <= (less than or equal)
  • LIKE (to find similar patterns)
  • IN (finding matches according to a certain value)
  • BETWEEN (defining the range of values)

To make these operators work, include them right after the ‘WHERE’ clause.

4) ‘AND’ and ‘OR’ operators

To write a basic SQL query and effectively select the data from a certain column in a table, you also need to use ‘AND’ and ‘OR’ operators used together with the ‘WHERE’ clause.

The AND operator is used to combine two different conditions and gives you the results only if there is data that matches both these conditions. Here’s how the syntax with the ‘AND’ operator looks like:

SELECT column1_name, column2_name…FROM table_name
WHERE condition1 AND condition2

Respectively, the OR operator combines two different conditions and gives you the results if there is data that matches one of these conditions. The syntax, in this case, looks like this:

SELECT column1_name, column2_name…FROM table_name
WHERE condition1 OR condition2

To write a more complex SQL query, you can also combine the ‘AND’ and ‘OR’ operators, which will allow you to retrieve data that matches many different conditions.

5) ‘IN’ and ‘BETWEEN’ Operators

These operators help you select an exclusive variety of values based on certain conditions.

The IN operator allows you to select all the data that falls under a certain criterion. The syntax of the query, including the ‘IN’ operator, should look like this:

SELECT column1_name…FROM table_name
WHERE condition1 IN (value1, value2, value3…)

For instance, you can retrieve information about customers coming from the same country using this operator. Similarly, you can add ‘NOT’ to the ‘IN’ operator, if you want to exclude some results.

If you want to get results within a certain range of values, you can use the BETWEEN operator. In this case, the syntax will look as follows:

SELECT column1_name…FROM table_name
WHERE condition1 BETWEEN value1 AND value 2

As you can see, the ‘AND’ operator is also used to compare the values and fetch the results.

6) ‘ORDER BY’ clause

This clause is used when you want to put the fetched results in a certain order. The ORDER BY clause tells the server how to organize the data, and the syntax looks like this:

SELECT column_list…FROM table_name ORDER BY column_name

preferred order of the fetched data.

7) ‘LIMIT’ clause

If you want to limit the number of results, you can include the ‘LIMIT’ clause in MySQL or Top clause in SQL Server. The basic syntax using this clause looks like this:

SELECT column_list…FROM table_name ORDER BY value LIMIT number

As you can see, the ‘ORDER BY’ clause is also used in this case, because it helps identify the values to be limited.

8) ‘UPDATE’ statement

Once you’ve inserted and selected data, you can now proceed to writing an SQL query to update this data. To do it, you will need to use the UPDATE statement, for which the syntax looks similar to this:

UPDATE table_name
SET column1_name = value 1…
WHERE condition1, condition2…

The ‘SET’ clause allows you to identify a specific column or row that needs to be updated. You can also use all the above-mentioned operators, like ‘AND’ and ‘OR’ to update the table.

9) ‘DELETE’ statement

This statement allows you to remove one or several rows. The syntax for this query is simple and looks like this:

DELETE FROM table_name WHERE condition

The ‘WHERE’ clause specifies the record that should be deleted according to the condition assigned to it. Don’t omit it, otherwise, you will delete all the records from the table.

10) ‘TRUNCATE TABLE’ statement

However, if you do want to remove all the rows at once, the TRUNCATE TABLE statement will help you do it quicker than a ‘DELETE’ statement. The syntax for this query looks like this:

TRUNCATE TABLE table_name

While removing the rows from the table, this statement doesn’t actually delete the structure of the table. You can use this statement if you want to rework the entire table.

Step #5: Proofread Your SQL Query

If you’ve finished writing an SQL query and it still doesn’t work, you might have made a mistake along the way.

So, watch out for these most common SQL query mistakes and make sure you proofread your query to avoid them:

  • Misspelled commands. This is the most common SQL query mistake. To solve this problem, you can use an SQL editor to fix the broken SQL statements and clauses. If your query contains text under the TEXT constraint, you also need to proofread it. You can do it quickly using different thesis sites containing automated proofreaders.
  • The omission of punctuation signs. A proper SQL query also needs to have brackets and quotation marks to operate properly. So check your query for single quotes and double quotes and make sure everything displays properly.
  • Improper order of the statement. If your query doesn’t work, the statements in it might be in an improper order. Having syntax templates at hand might help if you don’t have enough experience writing SQL queries.

To make your SQL queries work, you need to write them while keeping in mind where performance problems might appear within your query. Then, if you get an error message, you might already have an understanding, where to look for an issue and how to solve it.


How to Write Simple SQL Queries

An SQL Query is used to retrieve the required data from the database. However, there may be multiple SQL queries that yield the same results but with different levels of efficiency. An inefficient query can drain the database resources, reduce the database speed or result in a loss of service for other users. So it is very important to optimize the query to obtain the best database performance.

Let us consider some sample tables to understand these different methods to optimize a query.

Customers : Table Customers contains the details of the prospective customers for a shop.

CustomerIDLastNameFirstNameAddressAge
73001SmithJohn45 Jump Street21
73002ParkerAnna83 Wild Avenue45
73003JamesJosie99 Chestnut Avenue25
73004WhiteAnna55 Paper Street72
73005SparksHarry11 Wisteria Lane23
73006ParkerJane12 Quentin Road50

Products : Table Products contains the details of the products available in the shop.

ProductIDProductNameProductPrice
1001Shampoo100
1002Tooth paste20
1003Soap15
1004Hand Sanitizer50
1005Deodorant100

Orders : Table Orders contains the details of the products ordered by the customers from the shop.

CustomerIDProductIDProductQuantity
7300110035
7300110011
7300310021
7300410032
7300410051

Now that we have analyzed the tables Customers, Products and Orders, the different ways to optimize a query are given below with query examples from these tables:

1. Provide Correct Formatting for the Query

It is very important to provide the correct formatting while writing a query. This enhances the readability of the query and also makes reviewing and troubleshooting it easier. Some of the rules for formatting a query are given below:

  • Put each statement in the query in a new line.
  • Put SQL keywords in the query in uppercase.
  • Use CamelCase capitalization in the query and avoid underscore(Write ProductName and not Product_Name).

Example: This is a query that displays the CustomerID and LastName of the customers that have currently ordered products and are younger than 50 years.

Select distinct Customers.CustomerID, Customers.LastName from Customers INNER join Orders on Customers.CustomerID = Orders.CustomerID where Customers.Age < 50;

The above query looks unreadable as all the statements are in one line and the keywords are in lower case. So an optimized version is given below using the rules for formatting specified earlier.

SELECT DISTINCT Customers.CustomerID, Customers.LastName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.Age < 50;

2. Specify the SELECT fields instead of using SELECT *

SELECT * is used to obtain all the data from a table. So it should not be used unless all of the data is actually required for a given condition as it is highly inefficient and slows the execution time of the query. It is much better to use SELECT along with the specific fields required to optimize the query.

Example: This is a query that displays all the data in the table Customers when only the CustomerID and LastName was required.

SELECT * 
FROM Customers;

It is better to use the select statement with the fields CustomerID and LastName to obtain the desired result.

SELECT CustomerID, LastName 
FROM Customers;

3. Remove Correlated Subqueries if not required

A correlated subquery is a nested query that depends on the outer query for its values. If there are millions of users in the database, the correlated subquery is inefficient and takes a lot of time as it will need to run millions of times. In that case, an inner join is more efficient.

Example: This is a query that displays the CustomerID of the customers that have currently ordered products using a correlated subquery.

SELECT CustomerID
FROM Customers
WHERE EXISTS (SELECT * FROM Orders
              WHERE Customers.CustomerID = Orders.CustomerID);

It is better to use the inner join in this case to obtain the same result.

SELECT DISTINCT Customers.CustomerID
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Note: It is best to avoid a correlated subquery if almost all of the rows are needed from the database. However, in some cases, they are inevitable and have to be used.

4. Limit the results obtained by the query

In case only limited results are required, it is better to use the LIMIT statement. This statement limits the records and only displays the number of records specified. For Example: If there is a large database of a million records and only the first ten are required, it is better to use the LIMIT statement as this will ensure that only the relevant records are obtained without overtaxing the system.

Example: This is a query that displays the customer details with limit 3:

SELECT *
FROM Customers 
LIMIT 3;

5. Remove The DISTINCT Clause if not required

The DISTINCT clause is used to obtain distinct results from a query by eliminating the duplicates. However, this increases the execution time of the query as all the duplicate fields are grouped together. So, it is better to avoid the DISTINCT clause as much as possible. As an alternative, the GROUP BY clause can be used to obtain distinct results.

Example: This is a query that displays the distinct LastName of all the customers using the DISTINCT clause.

select distinct LastName
from Customers;

The distinct LastName of the customers can also be obtained using the GROUP BY clause which is demonstrated by the next example:

SELECT LastName
FROM  CUSTOMERS
GROUP BY LastName;

6. Avoid Functions in Predicates

Functions in SQL are used to perform specific actions. However, they are quite inefficient as they do not allow the usage of indexes which in turn slows the execution time of the query. So it is better to avoid functions in a query as much as possible to ensure its optimization.

Example: This is a query that displays the details of the products whose name starts with 'Sha'.

SELECT *
FROM Products
WHERE SUBSTR(ProductName, 1, 3) = 'Sha';

It is better to avoid the function and use the LIKE clause instead to obtain the same result.

SELECT *
FROM Products
WHERE ProductName LIKE 'Sha%';

7. Avoid OR, AND, NOT operators if possible

It is highly likely that indexes are not in use when OR, AND, NOT operators are used. In the case of large databases, it is better to find replacements for these to speed up the execution time of the query.

Examples of this for OR and AND operators are given below:

Example 1: This is a query that displays the details of the customers with CustomerID 73001, 73004 and 73005 using the OR operator.

SELECT * 
FROM Customers
WHERE CustomerID = 73001
OR CustomerID = 73004
OR CustomerID = 73005;

It is better to use the IN operator in this case to obtain the same result.

SELECT * 
FROM Customers
WHERE CustomerID IN (73001, 73004, 73005);

Example 2: This is a query that displays the details of the customers with age between 25 and 50 using the AND operator.

SELECT * 
FROM Customers
WHERE age >= 25 AND age <= 50;

It is better to use the BETWEEN operator in this case to obtain the same result.

SELECT * 
FROM Customers
WHERE age BETWEEN 25 AND 50;

8. Use WHERE clause instead of HAVING clause whenever possible

The HAVING clause is used with the GROUP BY clause to enforce conditions as the WHERE clause cannot be used with aggregate functions. However, the HAVING clause does not allow the usage of indexes which slows the execution time of the query. So it is better to use the WHERE clause instead of the HAVING clause whenever possible.

Example: This is a query that displays the Customer FirstNames with the count of customers who have them for the customers aged more than 25. This is done using the HAVING clause.

SELECT FirstName, COUNT(*)
FROM Customers
GROUP BY FirstName
HAVING Age > 25;

It is better to use the WHERE clause in this case as it applies the condition to individual rows rather than the HAVING clause that applies the condition to the result from the GROUP BY clause.

SELECT FirstName, COUNT(*)
FROM Customers
where Age > 25
GROUP BY FirstName;

9. Use INNER JOIN instead of WHERE clause for creating joins

Using the WHERE clause for creating joins results in a Cartesian Product where the number of rows is the product of the number of rows of the two tables. This is obviously problematic for large databases as more database resources are required. So it is much better to use INNER JOIN as that only combines the rows from both tables which satisfy the required condition.

Example: This is a query that displays the CustomerID of the customers that have currently ordered products using the WHERE clause.

SELECT DISTINCT Customers.CustomerID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID;

It is better to use the Inner join in this case to obtain the same result.

SELECT DISTINCT Customers.CustomerID
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

10. Avoid Wildcard Characters at the beginning of a LIKE clause pattern

Wildcard characters such as % and _ are used to filter out the results of a LIKE clause. However, they should not be used at the beginning of the pattern as this disables the database from using the index. In that case, a full table scan is required to match the pattern which consumes more database resources. So it is better to avoid the wildcard characters at the beginning of the pattern and only use them at the end if possible.

Example:

SELECT * FROM Customers
WHERE FirstName LIKE '%A%'

The above query is inefficient as it uses the wildcard character % at the beginning of the pattern. A much more efficient version of the query that avoids this is given below:

SELECT * FROM Customers
WHERE FirstName LIKE 'A%'

#sql #database 

What is GEEK

Buddha Community

SQL Tutorial | Write a SQL Query to Fetch Data from a Database
Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

 iOS App Dev

iOS App Dev

1625133780

SingleStore: The One Stop Shop For Everything Data

  • SingleStore works toward helping businesses embrace digital innovation by operationalising “all data through one platform for all the moments that matter”

The pandemic has brought a period of transformation across businesses globally, pushing data and analytics to the forefront of decision making. Starting from enabling advanced data-driven operations to creating intelligent workflows, enterprise leaders have been looking to transform every part of their organisation.

SingleStore is one of the leading companies in the world, offering a unified database to facilitate fast analytics for organisations looking to embrace diverse data and accelerate their innovations. It provides an SQL platform to help companies aggregate, manage, and use the vast trove of data distributed across silos in multiple clouds and on-premise environments.

**Your expertise needed! **Fill up our quick Survey

#featured #data analytics #data warehouse augmentation #database #database management #fast analytics #memsql #modern database #modernising data platforms #one stop shop for data #singlestore #singlestore data analytics #singlestore database #singlestore one stop shop for data #singlestore unified database #sql #sql database

Ruth  Nabimanya

Ruth Nabimanya

1621850444

List of Available Database for Current User In SQL Server

Introduction

When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current logged user in SQL Server

Get the list of database
Conclusion

#sql server #available databases for current user #check database has access #list of available database #sql #sql query #sql server database #sql tips #sql tips and tricks #tips

 iOS App Dev

iOS App Dev

1620466520

Your Data Architecture: Simple Best Practices for Your Data Strategy

If you accumulate data on which you base your decision-making as an organization, you should probably think about your data architecture and possible best practices.

If you accumulate data on which you base your decision-making as an organization, you most probably need to think about your data architecture and consider possible best practices. Gaining a competitive edge, remaining customer-centric to the greatest extent possible, and streamlining processes to get on-the-button outcomes can all be traced back to an organization’s capacity to build a future-ready data architecture.

In what follows, we offer a short overview of the overarching capabilities of data architecture. These include user-centricity, elasticity, robustness, and the capacity to ensure the seamless flow of data at all times. Added to these are automation enablement, plus security and data governance considerations. These points from our checklist for what we perceive to be an anticipatory analytics ecosystem.

#big data #data science #big data analytics #data analysis #data architecture #data transformation #data platform #data strategy #cloud data platform #data acquisition

Introduction to Recursive CTE

This article will introduce the concept of SQL recursive. Recursive CTE is a really cool. We will see that it can often simplify our code, and avoid a cascade of SQL queries!

Why use a recursive CTE ?

The recursive queries are used to query hierarchical data. It avoids a cascade of SQL queries, you can only do one query to retrieve the hierarchical data.

What is recursive CTE ?

First, what is a CTE? A CTE (Common Table Expression) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. For example, you can use CTE when, in a query, you will use the same subquery more than once.

A recursive CTE is one having a subquery that refers to its own name!

Recursive CTE is defined in the SQL standard.

How to make a recursive CTE?

A recursive CTE has this structure:

  • The WITH clause must begin with “WITH RECURSIVE”
  • The recursive CTE subquery has two parts, separated by “UNION [ALL]” or “UNION DISTINCT”:
  • The first part produces the initial row(s) for the CTE. This SELECT does not refer to the CTE name.
  • The second part recurses by referring to the CTE name in its FROM clause.

Practice / Example

In this example, we use hierarchical data. Each row can have zero or one parent. And it parent can also have a parent etc.

Create table test (id integer, parent_id integer);

insert into test (id, parent_id) values (1, null);

insert into test (id, parent_id) values (11, 1);
insert into test (id, parent_id) values (111, 11);

insert into test (id, parent_id) values (112, 11);

insert into test (id, parent_id) values (12, 1);

insert into test (id, parent_id) values (121, 12);

For example, the row with id 111 has as ancestors: 11 and 1.

Before knowing the recursive CTE, I was doing several queries to get all the ancestors of a row.

For example, to retrieve all the ancestors of the row with id 111.

While (has parent)

	Select id, parent_id from test where id = X

With recursive CTE, we can retrieve all ancestors of a row with only one SQL query :)

WITH RECURSIVE cte_test AS (
	SELECT id, parent_id FROM test WHERE id = 111
	UNION 
	SELECT test.id, test.parent_id FROM test JOIN cte_test ON cte_test.id = test.parent_id

) SELECT * FROM cte_test

Explanations:

  • “WITH RECURSIVE”:

It indicates we will make recursive

  • “SELECT id, parent_id FROM test WHERE id = 111”:

It is the initial query.

  • “UNION … JOIN cte_test” :

It is the recursive expression! We make a jointure with the current CTE!

Replay this example here

#sql #database #sql-server #sql-injection #writing-sql-queries #sql-beginner-tips #better-sql-querying-tips #sql-top-story