SQL Basics That Programmers and Database Administrators Must Know

Many programmers doubt whether SQL is a monster. However, in reality, it is one of the simplest declarative languages used for many decades now. However, for those who are used to the object-oriented programming languages, SQL behaves differently than the functional languages. However, some also argue that SQL to is functional, which is true to an extent.

In this article, we are trying to cover SQL in a bit more details for those who:

• Already used SQL, but didn’t completely understand it.
• Those who know it well, but never bothered about the syntax.
• Those who want to get trained on SQL or want to teach it to others.

Without further ado, let’s get dive into SQL.

> SQL Is Declarative

This has to be registered in your head first, “SQL is declarative.” It’s the paradigm in which you declare the nature of results as you would like to get it, but not how the computer may calculate those results. Isn’t it amazing? For example,

This is a very simple SQL declaration for anyone to understand. Here, you are not bothered about from where the employee records come from. You want them to have a decent salary figure.

If it is that simple to understand, then where do you find any problem with SQL? In fact, if there is a problem, it is the fact that most of the users tend to think it is imperative programming terms. The conventional programmers are used to the concept of asking the machine to do this or do that, but before doing it, just run a check and fail if so and so. This may include storing the temporary results in different variables, iterating, writing loops, calling some functions, and so on.

With SQL, you may forget about all these and start declaring things. It is not about ordering the machine to do something and get you the output but to declare what you want.

> SQL Syntax

One common confusion people have is that the SQL syntax is not in the right order as to how it is executed. In fact, lexical ordering of SQL syntax is:

• SELECT – FROM – WHERE – GROUP BY – HAVING – UNION – ORDER BY

In fact, all SQL clauses are not listed here. In fact, the lexical ordering of the syntax differs fundamentally from the actual logical order. This may also differ from the execution order based on the optimizer choices. In fact, not all databases may implement the things in the same order as like in MySQL, SQLite, and PostgreSQL, etc.

What we can understand from this is the fact that one should remember both the logical order and lexical order of the SQL clauses in order to avoid the common mistakes. If you can understand it, then it may become obvious as to why something may work, and others don’t work. However, it would have been better for many if the language would have been designed in such a way that the lexical order reflects the logical order, for which can take Microsoft’s LINQ as an example.

> SQL Table Reference

With the given difference between the logical and lexical ordering, many beginners may get tricked by thinking that the column values are first-class citizens here at SQL. However, as the experts of RemoteDBA.com point out, the column values are not in real. The vital things are table references. 

SQL standard tends to define the FROM clause as:

Here, the real “output” of the FROM clause is the combined table reference. This procedure will combine the table reference for the degree of a plus the degree of b. IF there are 3 columns in a and 5 columns in b, If a has 3 columns and b, then the output tale may be having 3+5=8 columns.

The records in these combination references may be those of the actual cross-product or the cartesian products of a x b. In other terms, each record of a gets paired with each of the b records. If there are 3 records in a and 5 records in b, then the combined table reference as mentioned above will be producing 3×5=15 records.

If we try to look at it from the set theory or relational algebra point of view, then the SQL table can be defined as a set of tuples. In this, each of the SQL clauses can be transformed into one or many relations to create new relations. So, in SQL, you have to always think in terms of the table references to understand how the data gets pipelined through the SQL clauses.

JOIN operations in SQL

There are five distinct flavors of JOIN operations in SQL as:

EQUI JOIN

ANTI JOIN

SEMI JOIN

CROSS JOIN

DIVISION

You can see that these terms are otherwise popularly found in relational algebra. In SQL, different terms are used to represent these concepts.

• EQUI JOIN: This is the most popular JOIN operation in SQL with two subsidiaries as: INNER JOIN and OUTER JOIN

• ANTI JOIN: This is another relational concept in which you can produce this by adding the NOT keyword simply to the IN or EXISTS predicates, as we describe below in SEMI JOIN. This same rule applies in terms of readability, performance, expressivity, etc. There is also a small caveat here as NULLs while using the NOT IN, which we may discuss in the forthcoming tutorials.

• SEMI JOIN: In SQL, it can be expressed in two different ways as

• Using IN predicate 

• Using EXISTS predicate

        Semi means half as these types of joins combine only “half” of the table references.

• CROSS JOIN: It may produce a cross-product of joined table references by combining each record of the first table to all records of the second table reference. As we saw above, this can be easily achieved using simple table references that are comma separated. In some rare cares when it is desirable, you may also explicitly write CROSS JOIN.

• DIVISION: If JOIN means multiplication, then the division is the inverse concept. Relational divisions may be monster beasts in SQL to express. As it is a bit tougher for beginners to understand, we will discuss DIVISION further in advanced tutorials.

In fact, as like any other development and functional language, SQL is also continually undergoing some version changes and updates which you need to keep track of in order to follow the most advanced and functional SQL programming practices.