What is a SQL Query?

By: Koen Verbeeck   |   Updated: 2021-08-31   |   Comments   |   Related: > TSQL

Problem

I'm learning about relational database management systems and oftentimes the term "query" is used in the documentation. What is a database query and how can I write one?

Solution

Databases are used to store data, typically in the format of tables. However, databases wouldn't be really useful if there wasn't a way to get the data out again. This is what we use the SQL programming language for, which stands for "Structured Query Language". When working with SQL Server, the database management system of Microsoft, we use a dialect of the SQL language called T-SQL (or Transact-SQL).

Using T-SQL, we can do various actions on our database: insert data, update data, delete data or select data from one or more tables. But there's much more: we can also manage security, create tables and other structures, and so on. In SQL, any text that the database can recognize as a command is a "statement". A collection of statements is typically called a "script".

How to Write and Execute SQL Queries

First, we need a client tool that is able to connect to the database server. Microsoft provides a couple of options:

• SQL Server Management Studio – A free client to manage your SQL Server infrastructure (on-premises and in the cloud) and to write SQL statements. You can find a step-by-step installation guide in this tip. SSMS can only be installed on Windows machines.
• Azure Data Studio – A relative new tool with more emphasis on query writing. It supports notebooks. It's cross-platform, meaning you can also install it on macOS or on Linux. A step-by-step tutorial is provided in this tip.
• SQL Server Data Tools – A specific type of project in Visual Studio. With this project, you can manage your database objects (such as tables, views, stored procedures and so on) inside a code environment. You can check in your database code into source control, such as Git. SSDT can be used as a starting point for automated database deployments. The following tip gives you an idea of what kind of features are available in SSDT: Top Features in SQL Server Data Tools for Database Projects.
• sqlcmd – A command-line utility to execute T-SQL statements. Check out the tip Introduction to SQL Server's sqlcmd utility for more info.

For the remainder of the SQL tutorial, we'll use SSMS to connect to our SQL Server instance and write T-SQL statements. When you start SSMS, you will be asked to connect to a database instance:

Depending on your environment, you might have to enter different information into the text boxes. A dot as the server name is the same as using localhost or (local), which means the same machine as the one running SSMS. There are also various authentication methods:

If you didn't install SQL Server yourself, check with your administration which connection information you need to supply. Keep in mind, that if you're connecting to an Azure SQL database, you also need to specify the database name. You can do this by clicking on Options and then entering the database name under Connection Properties.

Once you're connected, you'll see the list of available databases in the object explorer:

You can right-click on a database and select "New Query" to open up a new (and empty) query window.

Now we're ready to write some SQL! If you want to try out some SQL statements on your own, you can use the Adventure Works sample database. Learn more about it in the tip AdventureWorks Database Installation Steps.

Different Types of SQL Queries

DDL (Data Definition Language) SQL Commands

DDL is the collection of statements used to define data structures. Examples are creating a database, dropping a table, changing a view and so on. This is sometimes referred to as "changing the database schema". Let's illustrate with a couple of examples.

The following DDL statement creates a new database called Test, using the default settings:

CREATE DATABASE Test;

You can execute a statement in a query window by hitting F5 or by hitting the green arrow:

Keep in mind that this will execute every statement listed in the query window. If you only want to execute one single statement, you can select it and then execute it. In the following screenshot, only the first statement will be executed as it is in the current selection:

You can only execute the CREATE DATABASE statement if you have the permissions to create new databases of course. In an open query window, execute the following statement to switch the "context" to our newly created database:

USE Test;

The database context of a query window determines on which database the current statement or script will be executed. You can see the current database in the dropdown window in the upper left corner:

You can also use this dropdown to change database instead of using the USE <databasename> statement.

The following DDL statement creates a new table in our database.  For the dbo.HelloWorld object, dbo is the schema name and HelloWorld is the table name.  Here is the syntax to create the database table, columns with the associated data type (integer, varchar, etc.) and whether or not NULL values are acceptable:

CREATE TABLE dbo.HelloWorld(
     ID INT NOT NULL
    ,MyMessage VARCHAR(100) NOT NULL
);

We can now see the table in the database:

There are other DDL statements possible for our table, such as creating an index for example, or creating a primary key or foreign key. However, DDL statements are not the focus of this article, as we'll see in the next section.

DML (Data Manipulation Language) SQL Commands

With DDL statements we have created a database and a table, but we haven't dealt with actual data just yet. To do this, we have DML or data manipulation language. DML is every type of statement that directly interacts with data, be it inserting, updating, deleting or retrieving. DML statements are what the documentation classifies as queries. Let's insert some data using the following INSERT statement:

INSERT INTO dbo.[HelloWorld]
(
    [ID]
   ,[MyMessage]
)
VALUES(1,'Hello MSSQLTips!');

This will insert exactly 1 row into our table.

To view our row, we need to use a SELECT statement.

SELECT *
FROM [dbo].[HelloWorld];

Here is our result set:

The * is a wildcard which is shorthand for retrieving all columns of the table. With a SELECT query, we can retrieve data from one or more tables (or views). You can find a good introduction in the tutorial SQL Server SELECT Examples, where it's also shown how you can filter data, join data (INNER JOIN, LEFT JOIN, RIGHT OUTER JOIN) from different tables, sort data with the ORDER BY clause either ASC or DESC, WHERE clause options, alias column names, etc.

Actually, when people talk about "querying with SQL", they usually mean selecting data from one or more database objects using the SELECT statement. With the SELECT statement of the previous example, we have "queried the HelloWorld table".

Our first INSERT statement uses the VALUES clause, but you can also use a SELECT statement as the source for the data:

INSERT INTO dbo.[HelloWorld]
(
    [ID]
   ,[MyMessage]
)
SELECT 2,'Hello Again!';

The advantage of using a SELECT statement is that instead of hardcoding values into the clause, you can select data from multiple source tables and insert the result into your destination table. You can learn more about INSERT in the tips SQL INSERT INTO SELECT Examples and INSERT INTO SQL Server Command.

We can retrieve our two rows with this SELECT statement:

SELECT [ID],[MyMessage]
FROM [dbo].[HelloWorld];

The difference is that we now explicitly stated the columns we wanted to retrieve from the table, which is considered a best practice.

We can update data with the UPDATE statement:

UPDATE [dbo].[HelloWorld]
SET [MyMessage] = 'This is an updated message!'
WHERE ID = 2;

More detailed info is provided in the tip SQL UPDATE Statement. We can also delete data with the DELETE statement:

DELETE FROM [dbo].[HelloWorld]
WHERE ID = 2;

More info about DELETE can be found in the tip The T-SQL DELETE statement. One special statement is the MERGE statement, which can be used to insert, delete and update at the same time. The MERGE statement takes source data (this can be a table or the result of a SELECT statement) and matches it against a destination table. New rows are inserted, matched rows are updated and rows present in the destination table but not in the source are optionally deleted.

Before we run a MERGE statement, let's insert our second row again – since we deleted it in the previous example – with this statement:

INSERT INTO dbo.[HelloWorld]
(
    [ID]
   ,[MyMessage]
)
SELECT 2,'Hello Again!';

We can now execute the following MERGE statement:

MERGE INTO [dbo].[HelloWorld] AS target
USING(
SELECT ID = 1, [MyMessage] = 'Hello MSSQLTips.com!'
UNION ALL
SELECT 3, 'Hello MERGE'
) AS source ON [source].ID = [target].ID
WHEN MATCHED THEN
UPDATE SET [target].[MyMessage] = source.[MyMessage]
WHEN NOT MATCHED BY TARGET THEN
INSERT(ID, [MyMessage])
VALUES([source].ID, [source].[MyMessage])
WHEN NOT MATCHED BY SOURCE THEN DELETE;

This single statement does the following:

• The source data comes from two SELECT statements with a UNION ALL, which results in two rows. One with ID = 1 (which will result in an update) and one with ID = 3 (which will result in an insert). The source data gets the alias "source".
• The destination of the MERGE is the HelloWorld table, which is aliased as "target".
• The source and target are matched using the ID column in the ON clause.
• If there's a match – meaning both IDs are the same – then we will do an update. In this case, the row with ID = 1 will get a new message.
• If there's no match in the target, which means there's a row in the source that doesn't exist in the target, the row will be inserted. That's row with ID = 3.
• If there's no match in the source, which means there's a row in the target that doesn't exist in the source, the row will be deleted. This is the row in the target table with ID = 2.

When we execute the MERGE statement and retrieve the data from the table, we can see row 1 has been updated, row 2 has been deleted and row 3 has been inserted.

Although MERGE can be useful, it has some disadvantages. Since everything is done in one single step, it's hard to figure out how many rows have exactly been affected by which operation. How many rows were updated? How many rows were deleted? There are also several bugs with the MERGE statement which still aren't fixed at the time of writing. Aaron Bertrand goes into detail in some of the issues in the tip Use Caution with SQL Server's MERGE Statement.

Other Types of Statements

Besides DDL and DML, there are other types of SQL statements available in SQL Server. You have for example permission statements, which are used to create users and logins, and assign permissions. Or session settings, like SET DATEFIRST. And you also have backup and restore commands, session broker commands and so on. These types of statements are out of scope of this tip. You can find an overview here.

Next Steps

• You can find a whole list of SQL Server Management Studio tips in this overview.
• There's a graphical query designer in SSMS. You can learn more about it in the tip SQL Server Management Studio Query Designer.
• For people new to databases: what is SQL Server? and Learning SQL Server.
• SQL Server SELECT Tutorial
• SQL Server Stored Procedure Tutorial
• SQL Server INSERT Tutorial
• Advanced Use of the SQL Server ORDER BY Clause
• Getting Started with SQL INNER JOIN
• SQL Server Join Example
• SQL EXISTS vs IN vs JOIN Performance Comparison
• Group By in SQL Server with CUBE, ROLLUP and GROUPING SETS Examples

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips