SQL FROM Query Examples and Syntax

By: Joe Gavin | Updated: 2024-04-16 | Comments | Related: > TSQL

Problem

You may know the SQL FROM clause is part of every SQL query reading data from a table, view, or function, and as a beginner you would like to learn more about it.

Solution

We'll look at several examples of using the FROM clause in the following sections of this SQL tutorial:

Create Table
Alter Table
Views
Aliases
JOINs Across Multiple Tables
Subqueries
DELETE FROM

The following examples were run in SQL Server Management Studio (SSMS) 19.2 against a Microsoft SQL Server 2022 relational database. The tables and data are a heavily slimmed-down subset of the Wide World Importers Sample Databases for Microsoft SQL Server.

Create Table

We'll need a couple of populated tables for the examples, so let's use the MyDatabase database.

USE [MyDatabase];
GO

First, let's create a database table named Customers with two fields, CustomerId of type int and CustomerName of type nvarchar(100). Here is the syntax:

CREATE TABLE [dbo].[Customers]
(
    [CustomerId] [int] NOT NULL, -- Column name, data type and null setting
    [CustomerName] [nvarchar](100) NOT NULL -- Column name, data type and null setting
);
GO

Next, populate the Customers table with some sample data via an INSERT statement with the T-SQL programming language.

INSERT INTO [dbo].[Customers]
(
    CustomerId,
    CustomerName
)
VALUES
(1, 'ABC Toys (Head Office)'),
(2, 'Tailspin Toys (Sylvanite, MT)'),
(3, 'Tailspin Toys (Peeples Valley, AZ)'),
(4, 'Tailspin Toys (Medicine Lodge, KS)'),
(5, 'Tailspin Toys (Gasport, NY)'),
(6, 'Tailspin Toys (Jessie, ND)'),
(7, 'Tailspin Toys (Frankewing, TN)'),
(8, 'Tailspin Toys (Bow Mar, CO)'),
(9, 'Tailspin Toys (Netcong, NJ)'),
(10, 'Tailspin Toys (Wimbledon, ND)');
GO

Next, create a table called OrderIds with two fields, CustomerId of type int and OrderId of type int.

CREATE TABLE [dbo].[OrderIds]
(
    [CustomerId] [int] NOT NULL,
    [OrderId] [int] NOT NULL
);

Populate the OrderIds table with some sample data.

INSERT INTO [dbo].[OrderIds]
VALUES
(2, 288),
(3, 93),
(3, 960),
(4, 964),
(5, 600),
(6, 294),
(6, 709),
(8, 582),
(8, 658),
(8, 665),
(8, 677),
(8, 869),
(8, 901),
(1, 36),
(1, 72);
GO

Additional Resources:

Alter Table

We'll add a column called Active of type bit to Customers that will store a 0 if a customer is inactive or a 1 if they're active.

ALTER TABLE [dbo].[Customers] ADD [Active] [bit] NULL;
GO

Make them all active except for where the customer is CustomerId = 10.

UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 1;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 2;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 3;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 4;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 5;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 6;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 7;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 8;
GO
UPDATE [dbo].[Customers]
SET [Active] = 1
WHERE [CustomerId] = 9;
GO
UPDATE [dbo].[Customers]
SET [Active] = 0
WHERE [CustomerId] = 10;
GO

Views

Now, let's create a view. Views are virtual tables based on the result set of a SELECT query. It can be thought of as a stored SQL query that's treated as a table. The code below will create a view called VCustomersActive over the Customers table that only shows active customers.

CREATE VIEW [dbo].[vCustomersActive]
AS
SELECT CustomerId,
       CustomerName,
       Active
FROM dbo.Customers
WHERE Active = 1;
GO

SQL SELECT statement with all columns from the view.

SELECT [CustomerId],
       [CustomerName],
       [Active]
FROM [dbo].[vCustomersActive];
GO

The screenshot below shows that only the active customers are returned.

Additional Resources:

Aliases

An alias is an alternative name for something. In SQL, an alias is a temporary name assigned to a column or table. The query below assigns the alias CustName to the CustomerName field in the Customers table.

SELECT [CustomerId],
       [CustomerName] AS [CustName]
FROM [dbo].[Customers]
WHERE [CustomerName] LIKE 'ABC%';
GO

The following query assigns the alias 'c' to the Customers table. Aliasing a table name here doesn't do much for us, but it becomes apparent that this is useful in a JOIN.

SELECT [CustomerId],
       [CustomerName]
FROM [dbo].[Customers] AS [c]
WHERE [c].[CustomerName] LIKE 'ABC%';
GO

Additional Resources:

Aliasing Tables in a SELECT Statement Example

JOINs Across Multiple Tables

Aliases are very useful when joining tables to combine results from more than one table. Here, we're joining the Customers and OrderIds tables to get the CustomerName and OrderId for any customer who's placed an order. It's perfectly legitimate to write the SELECT in the form TableName.Fieldname for each field we want. But this falls under the 'just because you can doesn't mean you should' category. It's just not that easy to read.

SELECT [Customers].[CustomerName],
       [OrderIds].[OrderId]
FROM [dbo].[Customers]
    INNER JOIN [dbo].[OrderIds]
        ON [Customers].[CustomerId] = [OrderIds].CustomerId
ORDER BY [Customers].[CustomerId];
GO

However, by aliasing the table name, we make the SQL simpler, more readable, and less ambiguous. This query replaces the table names Customers with the alias c and OrderIds with the alias oid. This query is easier to read.

SELECT [c].[CustomerName],
       [oid].[OrderId]
FROM [dbo].[Customers] AS [c]
    INNER JOIN [dbo].[OrderIds] AS [oid]
        ON [c].[CustomerId] = [oid].CustomerId
ORDER BY [c].[CustomerId];
GO

Additional Resources:

Subqueries

As the name implies, a subquery is a query within a query that returns a result set. A subquery is one way to get a list of customers who have placed an order. This query will return a list of DISTINCT CustomerIDs in the OrderIds table, meaning they have placed an order.

SELECT DISTINCT [CustomerId]
FROM [dbo].[OrderIds];
GO

And we get the values 1, 2, 3, 4, 5, 6, and 8.

DISTINCT CustomerIDs in the OrderIds table

This query creates the result set in a subquery and passes the result set to the WHERE clause.

SELECT [CustomerName]
FROM [dbo].[Customers]
WHERE [CustomerId] IN (
                          SELECT DISTINCT [CustomerId] FROM [dbo].[OrderIds]
                      )
ORDER BY [CustomerId];
GO

This SELECT clause is the equivalent of this:

SELECT [CustomerName]
FROM [dbo].[Customers]
WHERE [CustomerId] IN ( 1, 2, 3, 4, 5, 6, 8 )
ORDER BY [CustomerId];
GO

Additional Resources:

DELETE FROM

Deleting data from a table is in a SQL database one time where the FROM clause is optional. Both queries delete inactive customers from the Customers table, but including the FROM makes it more intuitive.

DELETE FROM [dbo].[Customers]
WHERE [Active] = 0;
GO

DELETE [dbo].[Customers]
WHERE [Active] = 0;
GO

Additional Resources:

Next Steps

We've seen some simple examples of the SQL FROM clause. Here are some tips with more information:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

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

View all my tips

Article Last Updated: 2024-04-16

SQL FROM Query Examples and Syntax

Problem

Solution

Create Table

Alter Table

Views

Aliases

JOINs Across Multiple Tables

Subqueries

DELETE FROM

Next Steps

About the author

Comments For This Article