Here we look at the Select command in SQL.
The Select command in SQL allows us to get hold of some of the data from our database. Select can also be used to set a variable as well.
From is used alongside a Select command when we want to specify which database table we want to run our query on.
Depending on how you're performing your queries, you may need to specify which database you're using first.
-- State which database we are using if required
USE only needs to be specified once, so if you run further queries after the initial query, you can remove the USE command.
The following Select command gets all the rows and columns (all the data) from the table called 'Products'.
-- Get everything from the table
SELECT * FROM Products
For increased performance, if we only need certain columns from the table we can specify the column names and only these columns will be returned.
-- Get only the product names, ids and prices from the table
SELECT Name, Id, Price FROM Products
Select can also be used to set the value of some variable.
In this example, we use the Select command in two ways, firstly to give a variable a value, and then secondly to retrieve the value. We also use the Declare command to create the variable before we can use it.
-- create a variable to hold today's date and time
DECLARE @NowDateTime datetime
-- set the variable to today's date and time
SELECT @NowDateTime = GETDATE()
-- retrieve the variable's contents (display in query window or pass to database caller eg. C# application)
Taking this a step further, you can use Select to set a variable to the value returned from a Select query. You may want to to do this if need a value elsewhere in your SQL query.
-- Get the product name to be used later in the query
SELECT @SpecialProduct = ProductName FROM Products WHERE ProductId = @SpecialProductId