SQL Terms Beginners Should Know – Part 2

This is the second part of my list of essential SQL terms for beginners. In the first, you learned what a table, relational database, and query are. Now, it's time for more advanced database terms. But don't be afraid! I won’t crush you with scientific definitions. I will explain everything simply so that you can easily understand.

If you want to review the basic SQL terms, see my previous article, SQL Terms Beginners Should Know. Remember to bookmark this article. It will be a useful source of information.

Without further ado, here are some more SQL terms you need to know.

SQL Terms

Alias

An alias is a name that we can define for a specific column or table in an SQL query. Usually, we use aliases to make our life easier: to display a more readable column name or to shorten a long table name.

Our table might have a convenient name like order. But what if our table is called orders_january_2020_shipped? What if it’s even longer and more complicated?

It would be inconvenient to write out the table’s full name each time we refer to it. For example, to refer to the column confirmed_payment in the table mentioned above, we would need to type orders_january_2020_shipped.confirmed_payment.

However, you can use the AS clause to define an alias, in this case, j, for orders_january_2020_shipped. Now, it will be easier to indicate orders already paid: j.confirmed_payment.

You can also use an alias if you are writing a long, complex SQL query. For example, you can include this in your query: j.confirmed_payment AS jcp. Now, for the rest of the query, you can just use jcp instead of j.confirmed_payment.

Be careful not to get lost in these names though.

Okay, I might have confused you a bit. Let me give you an example to show what I mean.

SELECT column as c
FROM table as t
WHERE t.c = "something";

Remember, an alias only exists for the duration of the query. If you later write another, separate query, you can name the columns or tables differently.

Aliases in SQL are very handy, and it is worth knowing how to use them. You will probably appreciate them most when working with SQL JOINs.

Null

NULL indicates that the data value in a specific table cell is missing or unknown. It is important to understand what NULL is and when it is used. Without a good understanding of NULL, you will find it difficult to work with databases.

First, something you absolutely must remember: NULL is not 0, and it does not mean empty! NULL occurs when the value in a given table cell is unknown or undetermined.

NULL values can be applied to any data type: integers, dates, VARCHAR values, or any other type of data. But we need to be careful when using NULL. When using any comparison operations including NULL operators, the result will always be NULL.

When learning about NULL, the topic of three-valued logic comes up. Do you want to know what this is? Read the article Understanding the Use of NULL in SQL Three-Valued Logic to learn more.

Join

JOIN is an SQL operator that allows you to combine data from two or more tables. It typically concatenates rows with equal values for specific columns. If you already know what primary keys and foreign keys are, the JOIN function is something that connects them.

Different JOIN types return different results. Here are several JOIN types:

• Inner JOIN returns all rows that match the indicated condition from both tables.
• Left JOIN returns all rows from the left table with matching values from the right table. If one of the rows has no matching values in the right table, NULL will appear.
• Right JOIN returns the same result as above only the other way around. The result will be all the rows on the right table with matching values from the left table, along with NULL where there is no match.
• Full JOIN returns all the rows from both tables.
• Cross JOIN returns all the combinations of rows from both tables. Here is our article What is CROSS JOIN in SQL.

Here are other use cases for JOIN:

• Self JOIN is a JOIN applied within one table, which involves using aliases. For more information, see the article What Is a Self Join in SQL? An Explanation With Seven Examples.
• Non-Equi Self JOIN is a type of JOIN with a non-equality in the ON condition. You can view practical examples of when to use non-equi JOINs in SQL to learn more.
• Multiple JOINS occurs when joining more than two tables. First, you JOIN two tables. Then, you JOIN another table to the first JOIN.

To learn more about and practice joining tables, check out our interactive SQL JOINs course. We also have a great SQL JOIN Cheat Sheet. Bookmark it in your browser. You will come back to it many times.

Primary Key

Not all columns have the same role. Some columns are primary identifiers for the table in question: they have different values in each row. This column is called the primary key.

A primary key is used to identify rows, and the uniqueness of a given record ensures that no duplicates will appear in the table. If you try to insert a new row with a duplicate value in the primary key column, that row will be rejected by the database.

For example, imagine the orders table. Most likely, it will have columns like product, customer, purchase_date, etc. However, to make everything clearer and easier, it is helpful to have an order_id column. Each line will have its own unique ID. This will be your primary key.

This is very important! No cells in the primary key column can be empty. This column cannot contain NULL values either. For a refresher on NULL, take a look at its section above.

For the orders table, a row with a NULL value in the order_id column will be rejected by the database. In other words, primary keys ensure that all rows in the table have unique and non-null values in the primary key column. Put simply, if it were otherwise, most likely not all orders would end up with your customers.

Do you want to know more about primary keys? Read What is a Primary Key in SQL? This great article has almost everything you need to know about the subject.

Foreign Key

It's one of those concepts that seems difficult and abstract, but it's actually simple. Foreign keys are a central concept in SQL databases. They help us maintain data integrity and usually work with primary keys to JOIN two tables.

Imagine two tables: product and customers. Both will most likely have the product_id column, which we can use to connect the product with a specific person who ordered it. In the second table, we will refer to this column as the foreign key. The foreign key checks whether the parent table (the one with the primary key) has such a value.

Do you want to know more about the foreign keys? I recommend What is a Foreign Key in SQL? by my colleague Ignacio.

If you want to master foreign keys by creating tables and their structure, I recommend the interactive SQL courses in Creating Database Structure. These five courses have over 330 exercises in total. If you are considering a Data Engineer career, this is perfect for you.

SQL Constraint

You already know that tables in relational databases must have a specific structure. However, there are times when you need to add additional restrictions to certain columns.

For example, in your company's customer table, the first_name, last_name, and client_id columns cannot be empty. Also, the email_adress column must have unique values. This is when SQL constraints come in handy.

• Not Null ensures that none of the column values is empty (i.e., it cannot contain a NULL value).
• Unique ensures that the column does not contain duplicate values (i.e., all values in the column must be unique).
• Primary Key ensures each column value uniquely identifies each row in the table.
• Foreign Key ensures a column linked to a primary key of a column in another table.
• Check checks the condition that follows.
• Default ensures that there is a default value in the column. This value is used when the user doesn’t enter a value in the column.

Thanks to SQL constraints, your tables will contain correct data that will be easier to analyze later. For more information and examples of how to use SQL constraints, check out What Is a SQL Constraint?

Subquery

Basically, a subquery is an SQL query within another SQL query. Instead of breaking your project into many small elements, you can write one logical SQL query to get the job done.

The power of subqueries is underappreciated. Many users are afraid of using subqueries even though they are simple and helpful.

There are several types of SQL subqueries:

• Scalar Subqueries return a single value, or exactly one row and exactly one column. They are most often found in WHERE clauses, where they help to filter the results of the main query.
• Multiple-Row Subqueries return one column and multiple rows. They are usually used with operators like IN, NOT IN, ANY, ALL, EXISTS, or NOT EXISTS.
• Correlated Subqueries are interesting. The inner query relies on information obtained from the outer query.

There are many combinations of and possibilities for subqueries. Do you want to know more? Check out the Beginner's Guide to the SQL Subquery by my colleague Ignacio. If you want to delve into the types of subqueries, read What Are the Different Types of SQL Subqueries?

I cannot explain SQL subqueries well in so few words. Please take the time to learn them well. You can do this with our SQL Basics course. You can also watch clips on our YouTube channel We Learn SQL in which my friend Adrian will tell you everything about SQL subqueries. Remember to subscribe and give it a thumbs up!

Database Tools

I assume that you will sometimes come across terms related to solutions and tools for working with databases. I have chosen three important ones to go over.

MS SQL Server

In the previous article, I told you about DBMS. If you don't remember what it is, you can go back and find out. In short, it is a program for working with databases.

One of the most popular DBMSs in the world is MS SQL Server. Its main disadvantage is that the full version is paid.

SQL Server 2019, codenamed Aris, is the 15th and latest edition of this tool. The first edition was established in 1989. It is the flagship, but not the only, database tool of the IT giant from Redmond.

MS SQL Server is based on Transact-SQL (T-SQL), which is an extension of the ANSI/ISO standard. T-SQL is standard SQL in which some things are defined slightly differently.

From the point of view of the casual user, there aren't any big differences. If you can use SQL Standard, you will be able to use T-SQL just as well. You can check the differences in the documentation.

If you would like to learn how to write SQL queries in MS SQL Server right away, I recommend this interactive SQL Basics in MS SQL Server course. You will learn the necessary basics and master the syntax enough to write your own queries through 131 exercises.

Are you not convinced to use a DBMS and still struggling with data processing in Excel? I don't mind this program. It's a powerful multi-tasker. But it has its limitations. So, why not use MS SQL Server? My colleague Roman will help answer this question in his Microsoft SQL Server Pros and Cons article.

PostgreSQL

PostgreSQL, also called Postgres, is another great DBMS tool. In my articles, I have repeatedly admitted that this is my favorite one. Why? First, it is open source, which means it's free.

Second, it has a huge community behind it. Passionate developers from around the world add new extensions and patches every day. This is one of the most active IT communities on the web. You can be sure that if you have trouble, someone will help you.

But you don't have to take my word for it. See who is using PostgreSQL by reading Which Major Companies Use PostgreSQL? What Do They Use It For?

You can easily use PostgreSQL in any project related to working with relational databases. Seriously, it's a brilliant tool.

If you are completely green, start with SQL Basics in PostgreSQL. You will learn the necessary basics to start working with PostgreSQL. Then I recommend the SQL Practice in PostgreSQL track. It will come in handy before the interview, before the exam, or simply when you want to review Postgres.

PostGIS

One of the most interesting PostgreSQL extensions is PostGIS. This is an add-on that allows you to store and process geographical data in PostgreSQL.

Geographical datasets (like coordinates for points on a map or boundaries of areas) can be enormous. It's often terabytes of data that would otherwise be difficult to access.

Thanks to PostGIS, this can be done quite easily and, above all, quickly. What would normally take hours may now only take seconds.

Do you want an example of a site using PostGIS? OpenStreetMap is one of them.

If this seems to be black magic to you, I recommend this PostGIS course. It is one of the best courses of this type on the market. It was our Course of the Month on LearnSQL.com, and I talked with its co-creator, Agnieszka Kozubek-Krycuń, who is the Chief Content Officer at Vertabelo about it.