What is a Composite Primary Key?

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

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

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

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

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

How NULL Values Can Affect your Results When Using the COUNT() Function in SQL

The SQL COUNT() function is a handy tool for telling us how many rows would be returned in a query. We can pass a column name to the function or we can pass the asterisk (*) wildcard to indicate all columns.

If a column contains NULL values, we could get different results, depending on whether we use the column name or the asterisk (*).

Continue reading

How the SQL UNION Operator Deals with NULL Values

The SQL UNION operator concatenates the results of two queries into a single result set. By default it returns distinct rows (i.e. it removes any redundant duplicate rows from the result set). But we can also use UNION ALL to return non-distinct rows (i.e. retain duplicates).

When it comes to NULL values, it’s pretty straight forward. SQL treats two NULL values as non distinct values. In other words, they’re duplicates.

Continue reading

How to Limit the Rows Returned by a SQL Query

When writing SQL queries, we’ll often use a WHERE clause or HAVING clause to narrow the results down to just those rows that we’re interested in.

But sometimes we might want to reduce the number of rows returned without adding extra filtering criteria. Sometimes we might just want to see a handful of rows, without hundreds, thousands or even millions of rows being returned.

Continue reading