Category: SQL

Understanding the SQL Subquery

Posted on by Ian

One of the things I love about SQL is just how easy it is to write a simple query that returns a meaningful result set, without having to a whole bunch of complex code. And I imagine most beginners are relieved when they discover this too.

However, while basic queries can go a long way, there’s a whole world of advanced techniques that can take our SQL development to another level. Somewhere in that world, would be the SQL subquery.

In this article, we look at the SQL subquery. We’ll explore what subqueries are, how they work, and when to use them. We’ll also look at some simple examples to demonstrate their use.

Continue reading →

How to Return Values in SQL Without using SELECT

Posted on by Ian

The SQL SELECT statement is possibly the most commonly used SQL statement. It’s often used to return data from a database, but it can also be used to call functions that return data. The SELECT statement can also be used to return static values, such as string literals.

But the SELECT statement isn’t the only way we can return data in SQL. Another way to return values is with the VALUES statement.

Continue reading →

What is a Composite Primary Key?

Posted on by Ian

Primary keys are a crucial part of relational database management systems (RDBMSs). Most of the time we create a primary key constraint against a single column that is the unique identifier for the table.

But we can also create primary key constraints against more than one column. When we do this, we call it a composite primary key.

Composite keys can be handy when we don’t have a single column that contains unique values, but multiple columns can be combined to create a unique value.

Continue reading →

Check if Table Exists in SQL

Posted on by Ian

With SQL we can use various methods to check whether or not a table (or other object) exists in the database. The method we use will often depend on the RDBMS we’re using, as well as the task we’re trying to undertake.

There’s usually a reason we’re trying to check for the existence of a table, and often the syntax we use will be tied to that reason. For example the ...IF EXISTS clause is a handy addition to the DROP TABLE statement, and the ...IF NOT EXISTS clause can often be used with the CREATE TABLE statement.

Other times we may simply want to see if the table exists without performing any immediate actions against that table. In such cases, we would need to run code specifically to see if the table exists.

Below are examples of code we can use in each of the above scenarios.

Continue reading →

SQL EXISTS Explained

Posted on by Ian

The SQL EXISTS predicate is used to specify a test for a non-empty set. It returns TRUE or FALSE, depending on the outcome of the test.

When we incorporate the EXISTS predicate operator into our SQL queries, we specify a subquery to test for the existence of rows. If there are any rows, then the subquery is TRUE. If there are no rows, then the subquery is FALSE.

Continue reading →

DROP TABLE IF EXISTS in SQL

Posted on by Ian

In SQL, we can use the DROP TABLE IF EXISTS statement to drop a table only if it exists.

While it may seem obvious that we can only drop a table if it exists (i.e. we can’t drop a table that doesn’t exist), there’s a good reason for using this statement.

The reason we put an IF EXISTS clause into a DROP TABLE statement is to prevent any errors that would occur if the table doesn’t exist.

Continue reading →

3 Ways to Remove Duplicate Rows from Query Results in SQL

Posted on by Ian

Sometimes when we run a SQL query, we only want to see unique rows. But depending on the columns we’re selecting, we might end up with duplicate rows. And this could happen without even knowing it, especially with large data sets.

But it doesn’t have to be this way.

Fortunately most SQL databases provide us with an easy way to remove duplicates.

Continue reading →