Tutorials
- ASP.NET
- C#
- C++
- Java
- JavaScript
- MySQL
- PHP
- Ruby
- SQL
- VB.NET
Code
How To
News
Forums
Pictures
Videos
Icons
Newsgroups

Building a C# Chat Client and Server

A step by step tutorial teaching you how to create your own chat client and chat server easily in C#, for local networks or the Internet.

in C# Programming Tutorials

Getting Hard Drive Information

A C# tutorial showing you how to make use of WMI to extract information on disk drives, such as model, capacity, sectors and serial number.

in C# Programming Tutorials

UPS Shipping Calculator

This tutorial will teach you how to calculate the shipping cost based on the weight, height, length and depth of the box, the distance and the UPS service type.

in PHP Programming Tutorials

Create Your Own Rich Text Editor

Creating a Rich Text Editor using JavaScript is easier to do than you might think, thanks to the support of modern browsers; this tutorial will walk you through it.

in JavaScript Programming Tutorials

Tutorials

Programming Tutorials

ActionScript (5)
ASP (7)
ASP.NET (28)
Assembly (1)
C (6)
C# (132)
C++ (33)
CSS (6)
General (4)
HTML (6)
Java (28)
JavaScript (38)
MySQL (2)
Objective C (1)
Pascal (1)
PHP (61)
Python (3)
Ruby (18)
Silverlight (1)
SQL (8)
VB.NET (5)
Visual Basic (3)

IT Jobs

From CareerBuilder

Geekpedia » SQL Tutorials » Using SELECT to retrieve data

Using SELECT to retrieve data

This tutorial deeply covers the SELECT statement of the T-SQL/SQL language. Starts from the basics - how to retrieve the values inside a column to ordering data, selecting multiple columns and then tables, joining them...

On Tuesday, October 5th 2004 at 01:27 PM

By Andrew Pociu (View Profile)

(Rated 4.7 with 66 votes)

Contextual Ads

More SQL Resources

What you will need for this tutorial:

Microsoft SQL Server 2000 any edition (even MSDE)

Microsoft SQL Server Enterprise Manager

Microsoft SQL Server Query Analyzer

The Northwind database (along with the pubs database) is included in any SQL server installation, except for MSDE (Microsoft SQL Server Desktop Engine). So if you don't have this database you can download an utility from MSDN which, if you run it, will install the two databases.

Using the SQL Query Analyzer

The SQL Query Analyzer is a tool where you can test your queries on the available databases. When you start the application you'll be asked to select a SQL Server and the authentication mode.

If you don't see any server name in the list click the '...' button and select a server from that list:

I recommend you select 'Windows Authentication'.

A query window is started but first a database on which the query will be executed must be selected. You can select a database from the toolbar on the top:

Select the Northwind database because this is the one we're going to use in this tutorial.

Using SELECT

Selecting a column

The SELECT command is the single keyword of DQL (Data Query Language - one of the categories in which T-SQL is divided).

Using this keyword you can retrieve data from the database. Let's test it a bit using Query Analyzer.

In the query window use the following query to retrieve a column (CategoryName) from a table (Categories):

SELECT CategoryName FROM Categories

Note that the keywords 'select' and 'from' are written all uppercase. This is not a requirement, more like a habit.

Now run this query by pressing F5 or clicking the '>' shaped button (or play) from the toolbar. The result is the following:

As you probably notice, the query we executed returns the contents of the CategoryName column from the Categories table.

If you didn't yet get it (although I think you did), here's how the query works:

This is a screenshot of the entire Categories table viewed with SQL Server Enterprise Manager.

Well, I think you realized from the first view what the query 'SELECT CategoryName FROM Categories' does. No wonder, as if you look in a dictionary for the term SQL you'll find something similar:

computer language: a standardized language that approximates the structure of natural English for obtaining information from databases.

Full form structured query language

Microsoft® Encarta® Reference Library 2003. © 1993-2002 Microsoft Corporation. All rights reserved.

If this query could be more obvious probably it would sound like 'Select the CategoryName column from the Categories table'.

Selecting multiple columns

You can select multiple columns from a table by separating their name with a comma:

SELECT CategoryName, Description FROM Categories

The result being the following:

Selecting all columns from a table

Sure, you can select all the columns from a table by specifying each column name, the way we saw earlier in Selecting multiple columns. But there's another way, a shorter one:

SELECT * FROM Categories

This can be translated in english like 'Select all from categories'. So it retrieves all the columns from that table without having to specify each one of them. It's well known that the * (asterisk) sign is often used in such cases.

Selecting only values that meet a criteria

Often when retrieving data from databases you need to return only values that meet a particular criteria. In our Categories table perhaps you may want to retrieve the description (available in the Description column) of a specific product, let's say of Condiments.

You may think you can retrieve all the values from that table and then, in your application, search for the one that is equal to 'Condiments'. That would be - pardon me - stupid!

Stupid, because you can retrieve only the value you are interested in by specifying this in the query, which is way more efficient:

SELECT CategoryName, Description FROM Categories WHERE CategoryName = 'Condiments'

This can be translated like "Select from the table Categories the value in the column CategoryName that is equal to 'Condiments'. Also select the description for that item".

Here you can see the result:

Selecting only values that meet more than one criteria

In SQL Server Enterprise Manager, still in the database Northwind, open the table Customers where you'll see lots of fictive company names, contact names and titles, cities, countries etc.. On ContactTitle column you can see many 'Owner' values. Of course, if you want to select only the people who are owners, you would do a "SELECT * FROM Customers WHERE ContactTitle = 'Owner'". Still, you want to find all the owners from Mexico. That's not hard at all:

SELECT * FROM Customers WHERE ContactTitle = 'Owner' AND Country = 'Mexico'

As you can see, the result is composed from 3 values. These 3 all are owners and live in Mexico. We retrieved this with the help of the AND keyword.

Sorting results

Using SQL you can sort results ascending on descending. When sorting numbers ascending means from 0 up to 1, 2, 3 and so on, and of course, sorting descending is the reverse (ex. start with 56, continue with 55, 54, 53...). When sorting by letters, ascending means you start from A and end with Z, and descending means you start with Z and end with A.

So, how do I actually sort?, you ask. Suppose you want to sort the values inside the Customers table ascending, by the name of the country:

SELECT * FROM Customers ORDER BY Country ASC

The list starts with Argentina and ends with Venezuela.

Selecting distinct values

In a table, for some obscure reason, you have duplicate records. Let's take the table we're currently working with - Customers. You want the list of all the countries where you have customers in. If you would just return all the countries as they are in the database you would get 'Argentina, Argentina, Argentina, Austria, Austria, Belgium, Belgium...' ...you get the point. There are no three Argentina's but there are 3 customers in that country, so for each one its country is returned. Here's where the DISTINCT keyword helps you out:

SELECT DISTINCT Country FROM Customers

So now duplicate values are eliminated and you're happy cause you get the list of countries without duplicates.

Selecting with another criteria if the other didn't met

Maybe you want to retrieve all the customers from Liechtenstein, but if there are no customers there, you want to retrieve the customers from Switzerland. You can do this using the OR keyword:

SELECT CompanyName, Country FROM Customers WHERE Country = 'Liechtenstein' OR Country = 'Switzerland'

In the table there's no customer from tiny Liechtenstein but there are two from Switzerland, therefore these will be displayed.

Selecting data from multiple tables

Most of the time when doing SQL queries you'll have to work with multiple tables.

Think about this: you have a table where you store your employees and one where you store the orders. Obviously, every row / field in the orders table represents an order. Each order has an employee who's responsible for it. Sure, for every order in the orders table you can specify the employees name, title, phone, address, hire date... But employees usually manage tens of orders, so to include all the employee information for each order is ineffective.

If you don't know already how this kind of stuff is done right with most databases, here it is:

In the employees table, each employee has an unique ID. In the Northwind databases, Davolio Nancy has ID 1, Fuller Andrew has ID 2 and so on... So why don't we just store this number instead of the name in the orders database. If one order is handled by Fuller Andrew, we'll store his id which is much shorter than storing all the information again and again for each order. Let's see how this works:

Using JOIN

INNER JOIN

Now, how can we join this two tables so that when we want to return information about an order, to have the name, title and phone of the employee that handles the order... to look like they're all in one database? Using JOIN.

Run the following query:

SELECT LastName, FirstName, Title, ShipAddress

FROM Employees INNER JOIN Orders

ON Employees.EmployeeID = Orders.EmployeeID

And see the result:

We joined two tables by specifying something that they have in common: the employee ID. LastName, Firstname and Title (in green) are columns from the Employees table and therefore provide information about the employee who's responsible for the order. ShipAddress (in red) is from the Orders table.

So what does our query say?

"Select the columns LastName, FirstName, Title and ShipAddress from the table Employees and the table Orders where there is a match between the values in column EmployeeID from the Employees table and between the EmployeeID column from the Orders table." - that's what our query says.

OK, you say, I understand that the JOIN keyword is used to join two tables using columns with ID's, but what's the role of the INNER keyword (in INNER JOIN)? By specifying INNER, only the rows that match are displayed. For example if in the Orders table there's one order that says it's being handled by employee ID 10 but inside the Employees table there is no Employee with ID 10, the order won't be displayed as it doesn't have a match in the Employee table.

There are other types of JOIN (FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN).

LEFT OUTER JOIN and RIGHT OUTER JOIN and FULL OUTER JOIN

As we discussed earlier, using INNER JOIN, the values that don't have a match aren't shown.

Using LEFT OUTER JOIN the situation is different. Let's take our case with the Employees and Orders tables and run this query:

SELECT LastName, FirstName, Title, ShipAddress

FROM Employees LEFT OUTER JOIN Orders

ON Employees.EmployeeID = Orders.EmployeeID

This query acts exactly like the earlier one, so it joins two tables. But because instead of using 'INNER JOIN' we used 'LEFT OUTER JOIN' if there is one value (more exactly ID) in the Employees table that doesn't match the value / ID in the Orders table, it will still be displayed.

For example I add a new employee named Art Vandelay with the title of CEO in the Employees table. Next I run the query shown above. What do we see if we scroll down to the bottom of the list? Even if there's no order handled by Art Vandelay inside the Orders table (maybe he's too lazy or he's recently hired), a row with LastName, FirstName and Title will still be shown. Of course, we don't have the value for ShipAddress because this is a column inside Orders table where, as I stated earlier, there is no match:

So the ShipAddress value is set to NULL.

The difference between LEFT OUTER JOIN and RIGHT OUTER JOIN is simple. While in LEFT OUTER JOIN the rows from the table specified in the FROM clause will still be displayed even if they have no match, in RIGHT OUTER JOIN the rows from the table not specified in the FROM clause will still be displayed, even if they don't match with the values in the other table, therefore the one specified in the FROM clause will have nulls.

So if you have an order handled by employee ID 23 (which doesn't exist in the Employees table), the order will still be shown but with the employee information set to null.

FULL OUTER JOIN works as you might expect, even if there's no match between neither one of the values in the two tables, the values will still be displayed... this is actually LEFT OUTER JOIN + RIGHT OUTER JOIN.

Specifying the table in which the column is

Let's take the first query we used to join two tables:

SELECT LastName, FirstName, Title, ShipAddress

FROM Employees INNER JOIN Orders

ON Employees.EmployeeID = Orders.EmployeeID

After the ON keyword, we specify the table from which we want the EmployeeID column, as both tables contain a column with this name... think at it, it wouldn't make sense to say '...ON EmployeeID = EmployeeID', the computer wouldn't know at what EmployeeID column you refer to.

This case also applies to the column names after the SELECT keyword - LastName, FirstName, Title, ShipAddress. What if in the Orders table you also had a column named Title? How is the computer supposed to know which one you want to select? How are you supposed to know what column you want to select when looking at your code after a few months?

In conclusion, always prefix the name of the columns with the name of the table even if there are no columns with the same name in the two tables, because you may add later. So here's how it should be:

SELECT Employees.LastName, Employees.FirstName, Employees.Title, Orders.ShipAddress

FROM Employees INNER JOIN Orders

ON Employees.EmployeeID = Orders.EmployeeID

Using UNION

Suppose you have two tables of employees (for some obscure reason), one named oldEmployees and the other newEmployees. You'll want to join them so you can see all the employees your company has. You can do this using the UNION keyword, and the query is straightforward:

SELECT FirstName, LastName FROM oldEmployees UNION SELECT FirstName, LastName FROM newEmployees

This will combine the columns of the two tables. Beware that you have to specify the same number of columns in the first SELECT statement as in the second SELECT statement. Also note that the columns need to have the same type (both varchar for example).

If the name of one employee is listed in both tables (which frequently happens with first names and last names), only one will be displayed. This can be changed by using UNION ALL instead of UNION.

There are some small things I didn't cover here, like CROSS JOIN, which I don't consider very important.

If this tutorial is appreciated other SQL tutorials will follow (on using INSERT, UPDATE, DELETE and more advanced topics). Oh, and feel free to comment on this tutorial

Digg It!

Del.icio.us Reddit

StumbleIt

Newsvine

Furl

BlinkList

Rate this tutorial

Current Comments

by Jon on Sunday, December 5th 2004 at 11:11 PM

I am learning about mysql queries and I have to say this has helped me learn about the SELECT query more than any other pages I've seen. I can't wait for the INSERT, DELETE, and UPDATE ones :)

by Gary on Saturday, December 11th 2004 at 08:27 AM

CROSS JOIN:
mysql> SELECT * FROM tab1e, table
each row in table1 crosses every row in table 2 once.
Higly useless if you ask me. Its slow and suggests you tables aren't normalized and u certainly won't do them on large tables deployed as part of datastores serving high volume web sites

by Gary on Saturday, December 11th 2004 at 08:28 AM

That was
SELECT * FROM table1, table2

by Gary on Saturday, December 11th 2004 at 08:30 AM

Oops, in addition the term CROSS JOIN was adapted by some, but its a classical CARTESIAN product derived from classical mathematics.

by prasad on Monday, December 20th 2004 at 02:28 AM

it's really nice

by johnfelix on Thursday, December 30th 2004 at 02:06 AM

Nice page.

I need to know how to select all tables in database from mssql database.

by Andrei Pociu on Thursday, December 30th 2004 at 03:30 AM

SELECT * FROM sysobjects WHERE type = 'U'

by Mohit Manaskant on Monday, January 31st 2005 at 01:56 AM

This is just excellent for beginners.It is fantastic

by Mohit Manaskant on Monday, January 31st 2005 at 01:57 AM

This is just excellent for beginners.It is fantastic

by Ashish A on Thursday, February 3rd 2005 at 06:32 AM

You can use SELECT statement for inserting records into a table.

Syntax:

SELECT <colsname> INTO <insert_table> FROM <select_table>

For Example: There are 6 records in a table named tableA and I want to insert them into a table named tableB. For that ..

SELECT * INTO tableB FROM tableA

Note: tableA and tableB should have same structure. Even this statement can create new table if insertTable is not created already.

by Vicki Pritchett on Saturday, February 5th 2005 at 10:06 AM

I am trying to learn about Oracle SQL. This site is really helpful. I have a question, true or false. If the number of tables to be joined =N, we need to include at least N-1 equality conditions in the select statement so that each common column is referenced at least once?

by Michael Mourtada on Saturday, February 12th 2005 at 11:14 PM

Thank you for taking the time to show us this sample.

by Grant Boettcher on Wednesday, March 9th 2005 at 01:07 PM

Very helpful. SQL question, I have an association table that stores an ID and numeric (1,2,3...) values that correlate to a users selection. How do I return the actual name of the selection if all I have is the numeric equivalent stored in the association table?
Thanks very much in advance
G

by Chelsea Owens on Thursday, April 28th 2005 at 01:01 PM

Very nice. Also, very informative and easy to understand. Thanks!

by cherry on Thursday, June 9th 2005 at 06:01 AM

galing

by cherry on Thursday, June 9th 2005 at 06:04 AM

question..
what's the code if i have to select data from three tables??

by cherry on Thursday, June 9th 2005 at 06:04 AM

question..
what's the code if i have to select data from three tables??

by ej on Tuesday, August 2nd 2005 at 09:24 PM

how to retrieve all field names in a table using msSQL? like describe tablex in mysql

by Andrei Pociu on Wednesday, August 3rd 2005 at 03:42 AM

I believe there are several ways.
I suggest you give a try to the <b>SP_HELP</b> stored procedure:

SP_HELP MyTable

More information at <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_304w.asp" target="_blank">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_304w.asp</a>.

by abhay kumar on Wednesday, August 31st 2005 at 07:02 AM

hi,

i want to know the sql quary for retriveing, deleting and count duplicate data from a table.

thanks

by omer kamal on Sunday, September 4th 2005 at 08:29 AM

i am working on a project in which i want to diplay table from my database. Is there some SQL statment for this reason??

like SELECT allTables FROM database

by omer kamal on Sunday, September 4th 2005 at 08:32 AM

i mean " i want to display all tables names a user can selcet from given database"

by Andrei Pociu on Monday, September 5th 2005 at 04:12 AM

I believe the "SP_Tables" stored procedure should do it...

by mansoorkhan on Friday, January 20th 2006 at 07:30 AM

i want to learn sql,this is fantastic,should iget the pdf file on my pc.

by focalguy on Wednesday, January 25th 2006 at 06:44 PM

Great tutorial. Really helped me to understand what the statements are 'saying' - especially the JOINs. I will use this information to teach some high school students.

by Anand on Tuesday, March 7th 2006 at 04:28 AM

In Oracle There is Something Called as DESC <TABLENAME>. Is there any alternative for this in Sql Server.

by D.Jagankumar on Thursday, May 18th 2006 at 08:57 AM

i need a base for sql server.i need to use normalization and for database.please send some basic tips and tricks to use sql server.so its easy to pickup my growth in software skills.

by Jeremy on Friday, July 7th 2006 at 12:44 PM

Wow...GREAT tutorial. The whole section on selecting data from multiple tables was spectacular. Learned a TON!

by Cyrus on Sunday, September 10th 2006 at 11:58 PM

is there any tutorial for northwind and pub for sql server 2000. Thanks

by manoj kumar sharma on Tuesday, January 9th 2007 at 02:47 AM

can u provide the table that contains syntax difference bet sql+ and sql query analyzer .

by Imran Khan on Friday, March 9th 2007 at 06:00 AM

How to select all rows from two different tables even then there is no unique key that match to both tables??

by akhya Raj Muchahary on Friday, April 13th 2007 at 12:57 AM

goalpara, Guwahati,Assam.

by Koteswara Rao .J on Tuesday, May 15th 2007 at 04:59 AM

good plz clarify to retrieve data after using the delete command in sql server

by swapna on Monday, May 21st 2007 at 05:29 AM

It's simple.
How to retrieve data from two different queries which returns different no. of columns in jsp?

by Froilan on Friday, May 25th 2007 at 01:58 AM

It's really nice!

by madan on Monday, June 11th 2007 at 03:42 AM

i need to configure sqldataadapter. while doing so i don\'t need entire table to be binded to grid but only selected columns. which rows to be selected must be obtained as arguments from another page. here the arguments should be caught and the values should be passed on to the table query so that the table is queried.then the queried table contents must be binded to the grid and displayed.

by senthilkumar on Wednesday, June 13th 2007 at 11:26 AM

Dear friends,
I have a doubt in sql server database.If anyone having the solution please share with me please.

How can i retrieve a duplicate value from a table column.

Regards

S.Senthil Kumar

by prasad on Wednesday, August 8th 2007 at 06:29 AM

Hi i want query to get only field names of table in ms-access

by Arun on Monday, September 10th 2007 at 02:29 AM

Hi,
I have a table with Employee Id, Manager Id, Salary say it is arranged as:-
Emp ID Mgr ID Salary
01 2000
02 01 1000
03 01 1800
04 01 2100
05 02 1500

i need a query which displays the employees who is getting more salary than their respective managers, so the output should be:

Emp ID MgrId Salary
04 01 2100
05 02 1500.

Thanks,
Arun.L

by sagar on Wednesday, November 14th 2007 at 11:54 PM

I hv same table say \"Patient\" in 2 different databases that are present on different or same server, Now i want to get those different records from the \"Patient\" table that are present in these databases.
I m using sql 2005
Does anyone have any stored procedure or a SQL query that will solve my above problem.

by dara on Thursday, November 15th 2007 at 10:51 PM

hello

by Mohammad Hashem on Wednesday, December 26th 2007 at 06:36 AM

very good

by deepak on Monday, July 28th 2008 at 06:36 AM

by ashok on Sunday, August 10th 2008 at 09:35 AM

hi..i wanted to know how to retrieve data from 4 tables.there is 1 field in common to 3 tables..but the 4th one has only 1 field in common to another 1 table only..how can i retrieve data from all the 4 tables at once?in one query..using hibernate in project..so,cannot have more than 1 query.thanks.

by chaitanya on Tuesday, August 19th 2008 at 07:52 AM

hi

i want a query to retrieve all column names of a table.

can any one help me with that.

thanks

by mala on Thursday, August 28th 2008 at 08:54 AM

Hi,

i want a query to retrieve every 5th record in a table.what is the query ?? can anyone help me out !!!

Thanks.

by AMARDEEP on Sunday, October 5th 2008 at 07:01 AM

It excellent for biginers. It has real good stuff.

by ash on Monday, November 17th 2008 at 02:49 AM

how to get value from this query

select * from table1 where column1='city' union
select * from table2 where column1='city'

by How to put high-value in SQL on Monday, March 2nd 2009 at 10:37 AM

I am trying to find use the date which will be less than 0 for date as year but it dose not let me to run it

by saroj on Tuesday, July 21st 2009 at 01:42 AM

nice to know new things

by saroj on Tuesday, July 21st 2009 at 01:43 AM

nice to know new things

by Andrew S C on Wednesday, November 11th 2009 at 12:50 AM

Hi,
if my date format in the table is as dd/mm/yyyy and I want to retrieve it as mm/dd/yyyy. Can any one help me out and let me the query please...

Regards,
Andrew

by Zeekool on Thursday, November 26th 2009 at 06:05 AM

hi i am really want help if someone can help me i will be thanks

in this link there are explain for system i want the table and diagram for the whole system and the the entity
plz help

by zeekool on Thursday, November 26th 2009 at 06:07 AM

hi i am really want help if someone can help me i will be thanks

http://www.grc.upv.es/Software/bluemall/PM²HW²N 2008-BlueMallPaper.pdf

in this link there are explain for system i want the table and diagram for the whole system and the the entity
plz help