SQL Terms Beginners Should Know - Part 4

Here it is – another batch of must-know SQL terms. In the first two parts, we covered tables, relational databases, queries, aliases, SQL JOINs, primary keys, and subqueries. Last time, we moved to more advanced terms like INSERT INTO, GROUP BY, and HAVING. Are you ready to dive deeper into SQL terminology?

This is the fourth installment of our series on basic SQL terms for beginners (and everyone else). Don’t worry if you missed the previous articles; you can find Part 1 (essential SQL terms), Part 2, and Part 3 in our blog.

Don’t hesitate to return to earlier articles, even if you’ve already read them. The more you repeat this information, the better you’ll retain it. So, let’s learn some more SQL terms you need to know.

Even More SQL Terms for Beginners!

CASE

The CASE statement goes through multiple conditions and returns a value when the first condition is met. Once the condition is true, CASE returns the result and stops. If you have a situation when no conditions are true, it returns the value in the ELSE clause. If there’s no ELSE clause and no condition is true, it returns NULL.

CASE can be used in SELECT statements with the WHERE, ORDER BY, and GROUP BY  clauses. It also works with the INSERT statement.

The case statement in SQL can be used in two ways: assigning a result to a value or calculating a result from logical expressions. You can learn more about this in our article on using CASE with the SUM() function.

CTE / WITH Clauses

CTE is short for Common Table Expression; it is also called a WITH clause or a named WITH clause. That’s because the WITH keyword starts all CTEs.

CTEs were first introduced in SQL:1999 and were adopted into SQL Server in 2005. The main and the best thing about the CTE is that it simplifies and clarifies complex queries.

When you learn how to use CTEs, you will choose them over subqueries most of the time. Why? Because CTEs make your queries more readable by dividing them into smaller blocks.

With a CTE, you define a temporary named result set. It is temporary because it is not permanently stored anywhere. It exists and is available in the execution of SELECT, INSERT, UPDATE, DELETE or MERGE statements; when the statement has run, the CTE is discarded.

Here are some basic CTE rules:

• Start with the WITH operator.
• Specify the name you’ll use for the CTE. You can optionally also specify the column names, separated by commas.
• After the expression name, put the AS
• Write the query that will produce the desired result set.

The basic WITH clause syntax looks like this:

WITH subquery1_name AS
(SELECT … subquery1...),
subquery2_name AS
(SELECT … subquery2...)
SELECT … main query ... 

As you see in the syntax example, you can have multiple CTEs in one query. And these CTEs can reference themselves or another CTE in the query.

CTEs can be recursive or non-recursive. Recursive CTEs are more advanced and require the addition of the RECURSIVE keyword. They allow you to traverse hierarchical structures like bills of materials or organizational charts.

There are many ways of using CTEs. You can check them in our guide. You can also see some practical examples and learn more about why the WITH clause is worth using.

DISTINCT

Even if you’re an absolute beginner at SQL, you’re probably familiar with duplicate data. This happens because SQL queries will show all returned rows in the result set, even if they are identical. Fortunately, the DISTINCT keyword is here to clean things up by removing all duplicate values from your result.

DISTINCT works with SELECT and must appear before specified columns. It looks like this:

SELECT DISTINCT
  column_1,
  column_2,
  …,
  column_n
FROM table_name

You can use DISTINCT with one or more columns. But remember that SQL DISTINCT will look up ruther for a distinct row than a distinct column.

This is only part of the possibilities that DISTINCT offers. If you want to learn how to manage multiple columns or aggregate functions or use DISTINCT with GROUP BY, read this article. You can also really expand your knowledge with our complete A to Z SQL learning track.

IDE

IDE stands for integrated development environment. You already know that you need an environment where you can write your SQL queries. Sure, you can start using the command line, but soon you’ll need more than that.

An IDE combines a code editor, debugger, some automation, and basically everything you need to comfortably work with large databases. Maybe you’ve heard about Oracle SQL Developer, My SQL Workbench, or DBeaver? These are all IDEs.

If you are not sure which SQL IDE would be suitable for you, check our list of the best IDEs of 2021.

INTERSECT

This is one of SQL’s set operators. With INTERSECT, you can combine two SELECT statements. In return, you will get rows which are common to both SELECT statements. INTERSECT creates an intersection of the selected statements.

Take a look at a basic INTERSECT syntax:

SELECT column_1 [, column_2, …, column_n]
FROM table_1 [, table_2, …, table_n]
[WHERE condition]
 
INTERSECT
 
SELECT column_1 [, column_2, …, column_n]
FROM table_1 [, table_2, …, table_n]
[WHERE condition]

When you want to use INTERSECT, there are a few things to remember. When you write the SELECT queries, the number and order of the columns must be the same. Also, the columns’ data types must be the same or at least be compatible.

You can learn more about INTERSECT here.

UNION

You have already met INTERSECT. Now it’s time for another basic set operator: UNION. This is also useful when you want to compare two or more tables. The difference is that UNION statements return only unique values.

For example, suppose you have two tables containing two people’s favorite movies. With UNION, you can merge these tables and get a result that doesn’t include any duplicates. (But if you want to know if those people like the same movies, you’d need to use INTERSECT.)

View

A view is an SQL statement that’s named and stored in the database. In other words, it’s like storing a predefined query; a view stores the query itself, not the query’s output. For more about SQL views, see this article.

You can create a view from one or many tables. It can contain selected rows from a table or all of them. The way the view structures the data is really clear and intuitive. You get summarized data, which can be very useful, e.g. to generate reports.

To beginners, views and CTEs seem quite similar. If you want to know when to use each one, check out this article on the difference between CTEs and views in SQL.

WHERE

WHERE is one of the most important clauses in SQL. It’s almost as common as SELECT.

WHERE basically describes what you are looking for in your query. You can use it with numbers or text to return one value or a range of values. WHERE can also be used with DELETE to remove some data or with UPDATE  to modify a record.

Here’s how WHERE looks with numbers:

SELECT *
FROM table
WHERE id = 2021

And here’s WHERE with text:

SELECT * 
FROM best_sailors 
WHERE FirstName = 'Yannick' AND LastName = 'Bestaven'

WHERE, as you can see, is a very powerful tool. It gives you many options when working with data.

Window Functions

Window functions are another great tool for those who want to sharpen their SQL skills. These functions perform calculations across a set of rows that are related to the current row. The ‘window’ in window function is because this set of rows is called a ‘window’ or ‘window frame’.

Window functions allow you to perform statistical analyses, such as ranking data based on the most sales, calculating running revenue totals, or using moving averages to analyze stores’ performance. They are invaluable when you want to compare data over time and track trends.

Basically, window functions help with everything that’s important in business analytics. If you want to see some simple practical examples, see our article on using window functions. If you know you’ll need window functions, here is a great interactive Window Functions course.

Read, Repeat, Remember

Well done – you’ve just expanded your knowledge of SQL terms! But you’re not finished. Remember:  the more you read, repeat, and learn, the faster you will become confident discussing (and using) SQL.

To improve your SQL skills, try LearnSQL’s online courses and regularly visit our blog. And don’t forget to subscribe to our newsletter so you’ll stay informed about new articles and promotions!

Are there some SQL statements or terms you’d like to see in these articles? Tell us in the comments. They might be in our next roundup of SQL terms for beginners. Stay tuned!