Select Query

The select query is the most common query in sql. It is used to fetch data from your database. If you work with a relational database, you will work with the select query extensively. Having a thorough knowledge of the select query, is certainly an added advantage to your team and to yourself.

A select statement comprises of a select expression and an optional order by clause. The select expression is the core part of the select query

The select expression is of this format

SELECT <<ColumnReferences>>
FROM <<TableReference>>
WHERE <<Predicate>>

For this write up, we are going to an hypothetical database for examples. The database will be about the products inventory of a shop. The database comprises of three tables; Products, Order_Details and Product_Details.

Products
ProductId	ProductName	Price

Order_Details
OrderId	CustName	OrderDate	OrderQuantity	OrderPrice

Product_Orders
ProductId	OrderId

So let us see how we can fetch data from this database. We are to fetch the data of all products that the shop has. All we are going to do is to write a simple select expression, that query for product in the products database. It will look like this. SELECT ProductId, ProductName, Price
FROM Products

WHERE

The last example did not need the where clause, but most of the time, the data you want to fetch is specific. In that kind of situation, the where clause helps you to determine which data to return. The where clause goes in the format below.

WHERE <<Predicate>>

The predicate can be in any of the following formats

Name	Symbol	Description	Example
Comparison	>,<,=	Test if the value column is greater, lesser or equal to a value	ProductId > 5
Member	In	To test if the value of a column is in a set of values	Price IN(500, 450, 600, 700)
Pattern	Like	To test if the value of a column matches a pattern	ProductName LIKE N%
Range	Between	To test if the value of a column is in a range of values	Price BETWEEN 300 AND 1000
Null Checker	Is Null	To test if a column reference is assigned a value	ProductName IS NULL

Let us try an example that will need the where clause. We will query for all product Id and Price of products that their price is lower than ₦200.

SELECT ProductId, Price
FROM Products
WHERE Price < 200

We should try another example that checks patterns. Let us query for the product id of products that their names start with 'h'.

All we have to do is to check against a pattern, but there is this problem of creating a pattern. To create a pattern, there are three things that will be of use, which are.

• Percentage Sign(%): Used to represent generic numbers of letters, with accept any letter
• Underscore(_): This is used for just one letter, but accepts any kind of letter
• Letters: Represents only the letter

  With that we can create our pattern, knowing that the name has to start with 'h', and continue with a generic number of letters. We have the pattern has h%. SELECT ProductName
FROM Products
WHERE ProductName LIKE h%
  

  With that example, you saw that the column reference does not have to be every column in your database, you can select the columns you want your query to return.

  JOIN

  A lot of time, the data you want to fetch, is not just from one query. You might want to get data from two, three or more tables. The sql standard provides the join clause to make it easy for you to get data from multiple tables.

  Let us try to get all the products that has been ordered, with their Order Id. This will comprises of data from the Products table and the Product_Order table. All we have to is to use a join of product and Product_Order as our table reference.

  SELECT ProductId, ProductName, OrderId
FROM Product INNER JOIN Product_Order
  

  We did not add the where clause, because we did not need to filter any part of the data. We should try another example. What of pulling data from three tables. Let us get fetch the Product name, Product Id and the Order Date of all ordered product that their order quantity is greater than 2.

  All we have to do, is to make sure we join tables that are linked. The products table and the Product_Order table are linked at the ProductId column. Product_Order table and the OrderDetails table are also linked at the OrderId column. So, with this, we can join ProductId and OrderDetails. By joining ProductId to Product_Order, then joining then to OrderDetails.

  SELECT ProductId, ProductName, OrdetDate
FROM Product INNER JOIN Product_Order
ON Product.ProductId = Product_Order.ProductId
INNER JOIN OrderDetails
ON Product_Order.OrderId = OrderDetails.OrderId
WHERE OrderDetails.QuantityOrdered > 2
  

  A table like Product_order table is called a linking table. When you have two tables that are not linked, try to look for a linking table or any other that links them. If you are also trying to design a databse, make sure you have a linking table.