SQL: Data Types, Synonyms, and Operators

As the data needed gets more specific, it’s likely more conditions will need to be added to the WHERE clause. It’s also more likely that synonyms will become more useful as SQL statements get longer. In this post we look at both.

SQL including Synonyms

In the previous entry, we looked at the overall syntax structure for a simple SELECT statement.

Getting Started with SQL (Structured Query Language)

Suppose we need to query some customer data and are given the following tables:

Table: Customers
Fields: Customer_ID NUMBER, Name VARCHAR, Signup_Date DATE
Table: Customer_Info
Fields: Customer_ID, Address VARCHAR, City VARCHAR, State VARCHAR, Zip VARCHAR, Phone VARCHAR

Notice the field names have VARCHAR, INTEGER, and DATE next to them. These denote the type of data that can be stored in this particular field. VARCHAR fields (variable character) allows for letters, numbers, symbols and spaces. Sometimes this may include a number, for example VARCHAR(30), which means the field is limited to 30 characters.

The next data type is INTEGER. This data type is limited to whole numbers. Trying to set an INTEGER field to something other than whole numbers will result in an error.

Lastly, we also have DATE which as one might guess is used to store dates. The format of the date will depend on the database being used.

Databases have many different data types available and the types themselves will even vary between database types. So the data types in Postgresql and Microsoft SQL Server may not be exactly the same, but there is likely to be similar data types between the two.

From here, suppose we need to query this data to get a customer’s name and address together. To do this we will have to JOIN our tables together. Joins will be the subject of my next SQL post. Suppose we come up with the SQL below:

Trying to run this SQL would give us an error. Can you guess why? Depending on which database is being used, the error may be something like, “Column ambiguously defined.” Anytime two or more tables contain a field with the same name and we use that field in the SELECT or WHERE sections of a query, we must tell the query which field is being used. Look at the last line of the query in particular:

Customer_ID = Customer_ID;

Customer_ID is also listed in the SELECT. So the question is how do we differentiate between the two Customer_ID fields? The general syntax is Table_name.Field_name. For this particular query, this may work just fine. We could write:

Select
Customers.Customer_ID
...
Customers.Customer_ID = Customer_Info.Customer_ID

Changing the lines above should let us run the query successfully. But what if our query is longer? What if we had more than 2 tables? What if our table and field names were particularly long? This can result in a lot of extra typing. This is where the concept of synonyms come in. Synonyms allow a letter or phrase to serve as a shorthand way to refer to a table name or field name. If looking at other SQL examples, synonyms will show up quite often. Below is an example of a common way synonyms may be done. The key section is the FROM section.

Notice the extra space after our table names and the letter “a” for Customers table and the letter “b” for the Customer_Info table. This doesn’t have to be a single letter, but often synonyms are very short to save keystrokes. Instead of lowercase letters, I could have used capital letters. Instead of ‘b’ for Customer_Info, we could have used something like Customer_Info c_info and the synonym would be ‘c_info’ instead. Using a slightly longer synonym like ‘c_info’ may help keep track of which table is being referenced in longer SQLs. In my experience these synonyms are not case-sensitive, meaning ‘a’ and ‘A’ would both be acceptable in the assignment and usage of the synonym.

Also notice the ‘a.’ and ‘b.’ (the period after the synonym is required as the synonym is taking the place of our table names) before the field names. This is denoting which table these fields are being pulled from. While not strictly necessary for the fields in the SELECT statement beyond Customer_ID, it is often useful to provide a synonym to all fields to help note which table a field is coming from or if changes to the SQL require more tables to be added. Adding another table with the ‘Name’ field would result in us seeing the ‘Column Ambiguously Defined’ error again if we did not tell the SQL interpreter which table the Name field was coming from.

Next, let’s discuss the common operators used in the WHERE clause. Suppose we needed to further refine which customer information we needed. If our customer database is very large, there may be 100,000+ rows on our Customers and Customer_Info tables.

The common operators used in the where clause are:

• = which denotes the two sides should be equal. In our example above, this is what tied our Customers and Customer_Info tables together by Customer_ID.
• <> is used for not equal to. This can be a bit trickier to use at times. One example could be, what if a promotion can not be sent to customers not in New York. The line could be something like b.State <> 'NY' which would exclude customers with a New York state address.
• > is greater than while >= is greater than or equal to. This means the expression or field on the left is greater than or greater than or equal to the field or expression on the right.
• < is less than while <= is less than or equal to. This means the expression or field on the left is less than or less than or equal to the field or expression on the right.
• IN is similar to = but will allow for multiple values to be entered. They should be surrounded by parenthesis and separated by commas. IN can sometimes be used when comparing field to fields instead of specific values. The most common usage would be something like this, b.State IN('TN','NC','SC','GA') if needing to add multiple states.
• NOT IN can be used to exclude a list of criteria. For example, NOT IN('NY','NJ')
• IS NOT NULL is another option. A Null is where no data is entered at all. Keep in mind, this is not simply a space/blank entered, that will appear as a blank space in the data. Nulls are often denoted as (null) or something similar in the database. Nulls are often the result of data being added via an INSERT or UPDATE. Suppose we had an ‘Address2’ field. Many Customers may not have a second line for their address and therefore may have their Address2 field be a null. Instead of including the NOT another condition could be IS NULL to return the Nulls for a field.

Now that we have seen these, let’s try to use a few:

A few notes:

• For the IN there is not a need for the extra space after the comma, it’s just there for readability.
• Rather than = NULL IS NULL is the correct syntax. Suppose the company acquired new customer records in the Southeastern US when they bought a smaller company. Maybe the Signup_Date was not imported properly or maybe it doesn’t exist.

Finally, let’s discuss the AND part of the where clause. Much like other programming languages, AND means all conditions must be true. So in this case, we’d only find the following information:

• Customer_ID from Customers must equal Customer_ID from Customer_Info for the information to match up. If a Customer_ID is on one table but not the other, it would not be returned in our results.
• The customer’s State must be in: TN, SC, NC, GA, AL, FL
• The customer’s Signup_Date must be Null.

If the rows being queried do not meet all three criteria, they will not be returned. This may take our database of 250,000 rows and return a result set of 5,000 rows or less.

This is it for the basics of SQL Syntax. With these tools a lot of querying can be done. In our next post, we will discuss joins as well as some other options we can use beyond AND. To me, the concept of SQL joins is one of the most important subjects to fully understand querying in SQL. A good understanding of SQL joins and the conditions that can be added with AND and OR can really allow complex SQLs to be created.