A quick and deep dive into 'SELECT'
A quick and deep dive into 'SELECT'

Once in a while I might need to write some SQL queries specially when I am working with php. Normally I do C# and prefer LINQ instead of SQL. Reason? Well, it's much easier and comfortable. Anyway, I have decided to have a few posts on basic SQL queries and a bit about their internal nitty-gritty because I wanted to have all my learning in a central location so that, I can find them whenever I need. So, let's begin...

There are two basic steps to write a simple SELECT statement. 
1. What do we want to retrieve? 
2. Where do we want the data to be retrieved from?

1. Column expression

The first step can be referred as column expression, because here we mainly write different column names that we want to retrieve from the database. we can write column names in many different ways.

SELECT FirstName -- One of the column names in Customer table
FROM Customer; 
SELECT Customer.FirstName -- Column name followed by table name
FROM Customer;

A new name can be shown in the query result by using "AS" keyword. If we are are going to have more than one word as the new name then we have to use double quotation mark or "[]" otherwise there is no need for it.

SELECT FirstName AS "Customer Name" -- Using double quotation
FROM Customer;           

SELECT FirstName AS [Customer Name] -- Using []      
FROM Customer;    

SELECT FirstName AS Customer_Name -- No double quotation or []
FROM Customer;

Using "+" sign, it is possible to combine two or more (string type) column names and their values.

SELECT FirstName + '' + LastName AS "Customer_Name"
FROM Customer;

It is also possible to accomplish other arithmetical calculations using "+, - , *, /" signs with appropriate data types.

SELECT  10*Discount AS Discounted_Price, FirstName -- arithmetic calculation
FROM Customer; 

It's also possible to use SQL Server Functions in the column expression.

SELECT  YEAR(BirthDay) AS Birth_Year,--get the Year part out of a datetime column value
MONTH(BirthDay) AS Birth_Month,--get the Month part out of a datetime column value
DATE(BirthDate) AS Birth_Date,--get the Date part out of a datetime column value
FROM CUSTOMER; 

Instead of writing individual column name, we can use "*" sign to get all the columns.

SELECT * FROM Customer; 

2. FROM clause

This simply means, which table(s) you want the data to be retrieved from. Normally, we just right the table name(s) but we can also use alias for table name(s). Notice, I have used (s) because we can also retrieve data from multiple tables at one go. More than one table name can be seen when there is a JOIN. Technically, a FROM clause can have one or more JOIN which we will go through in a future post.

SELECT * FROM Customer, Product; --retrieving data from more than one table    

3. WHERE clause

Beside the basic two steps, a SELECT statement can be decorated with "search conditions". It means if the value satisfies the condition given for a specific column then the value for each row appears under that column in the query result. 

A search condition can contain the following comparison operators: >, <, >=, <=, =, <>, != ( <> and != are the same. Both means 'Not equal to') and NULL. Remember, to use 'IS' or 'IS NOT' operators when using the NULL operators in the search condition. 

SELECT * FROM Customer
WHERE LEN(Customer.Name) >= 4; 

Above example is using a built-in function of SQL Server, that checks the length of an nvarchar(so called string) value and returns an int. So, in our WHERE clause we are going trough each Customer Name and checking if the length of the Name is equal to or greater than 4. If it is so, we are retrieving and showing those Name(s).

Search conditions can be extended by using the following operators: 'AND', 'OR', 'NOT'.

SELECT * FROM Customer
WHERE LEN(Customer.Name) >= 4 AND LEN(Customer.Name) <= 8 AND;

There are couple of other important operators we can use in our search condition. Such as 

IS, IS NOT

SELECT * FROM Customer  WHERE Customer.Email IS NULL;
SELECT * FROM Customer  WHERE Customer.Name IS NOT NULL;

IN, NOT IN

SELECT * FROM Customer  WHERE Customer.Age IN (18,19,20);
SELECT * FROM Customer  WHERE Customer.Age NOT IN (25,26,27);  

BETWEEN, NOT BETWEEN

SELECT * FROM Customer WHERE Customer.Discount BETWEEN 5 AND 10;
SELECT * FROM Customer WHERE Customer.Discount NOT BETWEEN 5 AND 10; 

LIKE, NOT LIKE

SELECT * FROM Customer WHERE Customer.Name LIKE '%HA%';
SELECT * FROM Customer WHERE Customer.Name NOT LIKE '_HA%';   

In the above example we used single quote not double quote because it's string. '%' and '_' are called wild cards where '%' replaces 0 to many characters and '_' replaces 1 character.

ANY, ALL

SELECT Name
 FROM Production.Product
 WHERE ListPrice >= ANY
     (SELECT MAX (ListPrice)
      FROM Production.Product
      GROUP BY ProductSubcategoryID);
 
 SELECT Name
 FROM Production.Product
 WHERE ListPrice >= ALL
     (SELECT MAX (ListPrice)
      FROM Production.Product
      GROUP BY ProductSubcategoryID); 
example source: http://technet.microsoft.com/en-us/library/ms187074(v=sql.105).aspx

There are couple of things to remember while using ANY or ALL. Both ANY and ALL are used to compare a value to a list or sub query. So, they must be preceded by =, !=, >, <, <=, >= (comparison operator) and followed by a list or sub query.

In case of 'list' a 'ANY' query means, the comparison between each value of the outer query is valid if at least one item of the list is satisfied. 
Eg: > ANY (1,2,3) means if any value from the outer query is greater than 1 or or 2 or 3 then that value will be shown.

In case of 'list' a 'ALL' query means, the comparison between each value of the outer query is valid if all of the items of the list are satisfied. 
Eg: > ALL (1,2,3) means if any value from the outer query is greater than 1 and and 2 and 3 then that value will be shown.

In case of 'sub query' both 'ANY' and 'ALL' queries work almost the same way as list. Instead of a list of values each item from the outer query is now compared with result of the sub query. 

In our case, for each Product subcategory, the sub query finds the maximum list price. The outer query looks at all of these values and determines which individual product's list prices are greater than or equal to any product subcategory's maximum list price, in the 'ANY' query. 

If ANY is changed to ALL (as shown above), the query will return only those products whose list price is greater than or equal to all the list prices 
returned in the sub query.

If the sub query does not return any values, the entire query fails to return any values.

The =ANY operator is equivalent to IN. Both the queries below give same result.

SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID =ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
 
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');       
example source: http://technet.microsoft.com/en-us/library/ms187074(v=sql.105).aspx

The <> ANY operator, however, differs from NOT IN: <> ANY means not = a, or not = b, or not = c. NOT IN means not = a, and not = b, and not = c. <> ALL means the same as NOT IN.

EXISTS, NOT EXISTS

SELECT * FROM Customer
WHERE EXISTS
(SELECT Customer.Name FROM Customer
    WHERE Customer.Name LIKE '%HA%');

While using EXISTS remember that the sub query does not actually produce any data. It returns a value of TRUE or FALSE.

4. ORDER clause

The last part of a SELECT statement is the ordering of data. There are only two keywords we can use to order the result data. One is 'ASC' = Ascending, which is default and the last one is 'DESC' = Descending.

SELECT * FROM Customer
WHERE LEN(Customer.Name) >= 4
ORDER BY Customer.Name DESC;           

 

May 28, 2014
Jahan Sarwar