Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Basic SELECT statement (with WHERE clause)
Published: Jul 08, 2013
This article is a simple and brief introduction to the SELECT statement within SQL Server. This is the most basic and most useful tool in the SQL language. It allows us to see what data is being held in our databases.

This is the manner in which we retrieve data from our database tables. As the name implies, we simply SELECT that data from a table. It’s that easy.

If you have not read the Basic INSERT Statement document then please run the following code as we will use the resulting table in our examples:

if OBJECT_ID('dbo.person') is not null drop table dbo.person
go

create table dbo.person
(
  
id smallint identity(1, 1) not null,
  
title varchar(4) not null,
  
firstName varchar(20) not null,
  
lastName varchar(30) not null,
  
hairColour varchar(10) null,
  
isParent bit not null,
  
dateCreated datetime not null default(current_timestamp),
  
modifiedDate datetime null,
  
constraint pk_person primary key (id)
)
insert into dbo.person
select 'Mr', 'Bart', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null union all
select 'Ms', 'Lisa', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null union all
select 'Mrs', 'Marge', 'Simpson', 'Blue', 1, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Homer', 'Simpson', null, 1, CURRENT_TIMESTAMP, null union all
select 'Ms', 'Maggie', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Peter', 'Griffin', 'Brown', 1, CURRENT_TIMESTAMP, null union all
select 'Mrs', 'Lois', 'Griffin', 'Red', 1, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Chris', 'Griffin', 'Blonde', 0, CURRENT_TIMESTAMP, null union all
select 'Ms', 'Meg', 'Griffin', 'Brown', 0, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Stewie', 'Griffin', null, 0, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Brian', 'Griffin', 'White', 0, CURRENT_TIMESTAMP, null


Firstly we’ll cover the most basic SELECT statement of them all… simply retrieving all columns from the table. To do this we use a special character, the star… *. In SQL Server, * simply means “all”…

select *
from dbo.person


This is a very quick and dirty way of seeing all the data in a table, but it is not a recommended practice. In fact, using *is generally frowned upon and using it is not a habit you should enter.

If you do wish to see every column in a table though, and with a sample of the data it contains, then * is your friend, but only when limited. For example you could have a table with 100 million rows… running “select * from table” could grind your system to a halt. Much better is to select a small portion just to get a feel for the data inside:

select top 2 *
from dbo.person


This will simply return the first 2 rows that SQL comes across (note that this is NOT order specific… it literally is the first 2 rows that SQL happens to find in the table. An order is more likely where clustered indexes are present, but that’s a whole separate discussion).

Anyway, it’s not likely that you will always want all the columns returned and therefore you can limit your result set by specifying your columns as required:

select firstName, lastName
from dbo.person


As you can see, getting the results you want is actually very straight forward.

In addition to this, what if you only wanted to return data for someone with the lastName of Simpson… that’s when the WHERE clause comes in as it allows you to filter data:

select firstName, lastName
from dbo.person
where lastName = 'Simpson'


You also don’t have to have the filter column in the select list. Here we’ll simply filter by the “isParent” flag:

select firstName, lastName
from dbo.person
where isParent = 1


As you can see, it’s incredibly easy to get some very specific data without much effort. You can also combine filter clauses… in this example we’ll get all parents with blue hair:

select firstName, lastName
from dbo.person
where isParent = 1
and hairColour = 'Blue'


It’s as simple as that. The only additional things I’d like to mention are that when using “null” you need to use “is” or “is not” rather than “=”…

select firstName, lastName
from dbo.person
where hairColour is null


And that you can use “between” with dates:

select firstName, lastName
from dbo.person
where dateCreated between '2012-01-01 00:00:00' and '2022-12-31 00:00:00'


So, as one last query, let’s select all non-parents with a lastName of Griffin and who have hair…

select firstName, lastName
from dbo.person
where isParent = 0
and lastName = 'Griffin'
and hairColour is not null


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.