Author: Ian

Understanding the Correlated Subquery in SQL

Posted on by Ian

When writing SQL queries, we sometimes find ourselves including a subquery in the query in order to get the results we want. Sometimes the subquery we include could be taken out of that query and run by itself to return its own independent result set. But not the correlated subquery.

A correlated subquery relies on the outer query for its data. We can’t simply move it outside of the outer query and expect it to work.

Let’s take a look the correlated subquery in SQL and observe it in action with a few examples.

Continue reading →

The Difference Between INNER and LEFT JOINs in SQL

Posted on by Ian

SQL joins are fundamental operations in relational databases, allowing us to combine data from multiple tables. Two of the most commonly used join types are INNER JOIN and LEFT JOIN. Understanding the difference between these joins is crucial if you intend to use joins in your queries.

In this article, we’ll look at the difference between the INNER JOIN and LEFT JOIN using a simple example.

Continue reading →

Using the WHERE Clause Effectively: Common SQL Operators and Their Usage

Posted on by Ian

The WHERE clause is a fundamental part of SQL queries that allows us to filter data based on specific conditions. Understanding how to use various operators within the WHERE clause can significantly enhance our ability to retrieve precisely the data we need.

This article explores some of the operators that are most commonly used with the WHERE clause.

Continue reading →

Fix Error 11415 “Object … cannot be disabled or enabled. This action applies only to foreign key and check constraints” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 11415 that reads something like “Object ‘UQ_Employees_Email’ cannot be disabled or enabled. This action applies only to foreign key and check constraints“, it’s probably because you’re trying to disable either a DEFAULT, UNIQUE or PRIMARY KEY constraint.

Continue reading →

Nested WHILE Loops in SQL Server: A Beginner’s Guide with Examples

Posted on by Ian

WHILE loops are a fundamental concept in T-SQL programming, allowing us to execute a block of code repeatedly as long as a specified condition is true. Nested WHILE loops take this concept further by placing one WHILE loop inside another, enabling more complex iterations.

This guide will walk you through the basics of nested WHILE loops in SQL Server, complete with simple examples and a demonstration of the BREAK statement.

Continue reading →

Understanding the Difference Between SCALE(), MIN_SCALE() and TRIM_SCALE() in PostgreSQL

Posted on by Ian

Anyone who looks at PostgreSQL’s list of mathematical functions will notice that there are some functions that have “scale” in their name. In particular, scale(), min_scale(), and trim_scale().

While these functions all have a similar name, they each have a different purpose. That said, they’re all related to the scale of the given number. The scale is the number of digits to the right of the decimal point in a number. For example, the number 1.95 has a scale of 2, and 1.958 has a scale of 3.

Continue reading →

Fix Error 1987 “Cannot alter nonclustered index … because its clustered index is disabled” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 1987 that reads something like “Cannot alter nonclustered index ‘UQ_Employees_Email’ on table ‘Employees’ because its clustered index is disabled“, it’s probably because you’re trying to rebuild a nonclustered index when the clustered index for the table is disabled.

To fix this issue, either enable/rebuild the table’s clustered index first, then try again, or enable all indexes at once.

The clustered index will typically be the primary key index (unless you’ve specified another clustered index for the table).

Continue reading →

Examples of SQL Subqueries in the FROM Clause

Posted on by Ian

When used in the FROM clause, a SQL subquery creates a temporary table that can be queried like any other table. These queries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.

In this article we look at three different examples of SQL subqueries that are placed into the FROM clause.

Continue reading →