article template

Different types of JOINS in SQL Server

JOINS fundamentals

In relational databases, such as SQL Server, Oracle, MySQL, and others, data is stored in multiple tables that are related to each other with a common key value. Accordingly, there is a constant need to extract records from two or more tables into a results table based on some condition. In SQL Server, this can be easily accomplished with the SQL JOIN clause.

JOIN is an SQL clause used to query and access data from multiple tables, based on logical relationships between those tables. In other words, JOINS indicate how SQL Server should use data from one table to select the rows from another table.

Enjoying CASE expression with SQL Complete

Being a declarative language, SQL provides little to no control over how inputs are dealt with. CASE expressions add much-needed flexibility to SQL queries and are crucial to master and use correctly.

The CASE Statement fundamentals

That's where SQL Complete hits the stage. Its advanced autocompletion capabilities allow writing the most complex CASE expressions quickly. In-built code snippets for CASE statements save your energy, highlighting pairs of matching keywords and quick navigation between them help minimize coding time as SQL CASE expressions can be long enough and combing through them is not an easy task.

SQL Complete also boasts a robust formatter that allows making code with CASE statements more readable and can help enforce common coding standards to a team or organization.

As part of our SQL CASE expression tutorial, we'll have a look at different MSSQL CASE statement examples with the help of the SQL Complete tool.

Grow with Devart

Learn more about special deals for corporate clients and other benefits!

SQL CASE statement types

There are two types of SQL CASE statements: a simple CASE statement and a searched CASE statement.

Simple CASE statements determine the result value evaluating an expression against multiple values.

Searched CASE statements determine the result value evaluating a set of Boolean expressions.

SQL CASE statement with multiple conditions

In case you need the result to satisfy multiple conditions, you can add those conditions to the CASE statement and combine them with the AND operator:

CASE expression
 WHEN condition1 AND condition2 THEN result1 
 ELSE result2 
END

You can also define a number of result values in the CASE statement by including as many WHEN/THEN statements as you want:

SELECT CASE expression
 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 ...
 ELSE result
END

Using CASE statements with the ORDER BY clause

CASE statements in the ORDER BY clause are used to change the output order and instruct the query to sort the results based on a certain requirement.

SELECT CASE expression
 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 ...
 ELSE result
END

Using CASE with the GROUP BY clause

To apply aggregate functions, you can use the CASE statement in conjunction with the GROUP BY clause.

Grouping with the CASE expression is a simple yet elegant method to arrange the query output in the required way.

SELECT CASE expression
 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 ...
 ELSE result
END

SQL CASE statement best practices

  • Multiple conditions in the CASE statements are processed in a sequential model. It stops checking conditions with the first successful condition.
  • It is better to use the ELSE block in CASE statements so that if none of the conditions is not satisfied, the default value will be returned.
  • CASE statement can not be used for checking NULL values in a table.
  • It is recommended to avoid conflicting conditions.

With SQL Complete, you GET

  • Code snippets for CASE expressions
  • Context-based prompts for table and column names
  • Navigation between the CASE and END keywords
  • Highlighting of matching keywords pairs
  • Quick info about database objects
  • Instant code formatter with built-in formatting profiles

Using the CASE Statement in SQL Server

The CASE Statement fundamentals

What is CASE in SQL?

SQL CASE statement evaluates a condition and returns a result that meets that condition. If none of the conditions is evaluated to TRUE it returns a value from the ELSE block. In simple words, the CASE expression is the way to build the IF - THEN logic into SQL.

A quick review of CASE rules
  • CASE must be followed by at least one WHEN... THEN expression
  • Every CASE statement must end with the END keyword
  • The ELSE argument is optional
  • CASE can be used in any statement or clause that allows a valid expression
  • Only 10 levels of nesting are allowed in SQL Server
Syntax example

The syntax for the SELECT statement with a simple CASE expression is as follows:

SELECT CASE expression
 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 ...
 ELSE result
END

Frequently asked questions about SQL Complete

Keep text of executed queries at hand

No need to copy a query text to a notepad after every change. Query Profiler keeps the query text along with its profiling results to let you optimize MySQL queries effectively. You need to select a required profiling result and click SQL Query. With query changes history, you can return to any step of the query optimization, review, execute, or save the query.

Compare query profiling results

The key to optimize MySQL queries quickly is to see the differences in profiling results after your changes. Using Query Profiler, you can select profiling results for two query executions and get the differences highlighted in the grid. Again, no copy-paste into a notepad is required.

Your ultimate MySQL IDE

MySQL GUI tool by Devart is an advanced IDE, whose unique features make it your perfect choice for MySQL database development and management. With smart data generation, SQL development, and comprehensive administration tools, our Studio is a viable alternative to MySQL Workbench.

dbForge Studio for MySQL

Get a free 30-day trial of dbForge Studio to evaluate all of its capabilities hidden under a sleek user interface.

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Query Profiler
Yes
Yes
Yes
None

Copy a table with data within the same Oracle database

Step 1

Oracle provides convenient syntax that helps implement the above-mentioned scenarios. By means of the “CREATE TABLE … AS SELECT … ” command, you can create a duplicate table within the same database schema. To create an exact copy of the table preserving the table structure and all the data, execute the query as follows:

SELECT CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

Step 2

If you want to limit your data copying to specific columns, indicate the column names after SELECT in the following way:

CREATE TABLE new_table_name
AS
SELECT column_name1,column_name2
FROM existing_table_name;

Step 3

Provided that you have already created a table, and you want to insert the data from the existing table into it, run the following:

INSERT INTO new_table_name
SELECT *
FROM existing_table_name;

Copy a table with data within the same Oracle database

Step 4

In some cases, you may want to copy the table structure but leave out the data. Similarly, you can use the "CREATE TABLE … AS SELECT … " command, but this time you need to modify it by adding the WHERE clause that is false and, consequently, will not select any data, for instance:

CREATE TABLE new_table_name
AS
SELECT *
FROM existing_table_name WHERE 1=5;

Copy a table with data within the same Oracle database

Step 5

To copy a table from one database schema to another one, you need to apply an SQL*Plus COPY command. This powerful command allows you to actually copy data between different servers. However, we will focus on copying a table between different database schemas in Oracle. The basic syntax for the command looks as follows:

COPY FROM source_database TO target_database action -
destination_table (column_name, column_name, -
column_name ...) USING query
Let’s define the important prerequisites for using this command:
  • You need to have the access to the specified tables and know the valid credentials for the local and remote databases in the FROM and/or TO clause.
  • You can indicate the new names for the columns in the destination table, otherwise, they will have the same names by default
  • You have to choose between four actions—REPLACE, CREATE, INSERT, or APPEND, the one that suits your particular case.
  • In the USING clause, you need to write a query that will be used to specify the data for copying. Here, you are free to use any type of SELECT.
Note: To create a new table and then copy the data, use CREATE; to fill the created table with data, use INSERT; to replace the created table together with its contents, use REPLACE. Besides, you can use APPEND, which works both ways: if you have created a new table, it will fill it with data; if you haven't, it will first create a table and then insert the necessary data.

Let's see an example of the COPY command that copies three columns from the source table and copies only the rows in which the EMPLOYEE_ID value is greater than 50:

COPY FROM [email protected] -
REPLACE DBCOPY1 -
USING SELECT FIRST_NAME, LAST_NAME, SALARY -
FROM DETAILS_VIEW -
WHERE EMPLOYEE_ID > 50

Create a duplicate table with dbForge Studio for Oracle

Running queries manually requires significant time spending, that’s why if you strive to be efficient, you will probably want to use an Oracle IDE that will help you achieve your goals within a convenient graphical user interface and boost your flexibility with an opportunity to adjust the process to your requirements.

SQL Complete is a smart solution designed to meet the increasingly sophisticated needs of SQL developers. Available as an add-in for Visual Studio and SSMS, the tool provides context-aware code completion, on-the-fly code refactoring, highly customizable code formatting, and much, much more.
SQL Complete well integrates into the IDE, significantly extending its functionality by offering a bunch of essential additional features. The solution is bound to speed up code creation and debugging as well as prevent occasional data or code losses.
dbForge Fusion for SQL Server is a multi-functional Visual Studio add-in for database development, management, and administration. The tool provides excellent SQL coding assistance and allows its users to debug T-SQL code, profile queries, manage changes, populate databases, and migrate data right in the IDE.
dbForge Fusion for SQL Server helps development teams significantly boost their productivity, improve code quality, and modernize database operations. Offering a bunch of coveted features, this powerful toolset greatly expands the limits of the IDE.
dbForge Fusion for Oracle is an advanced Visual Studio add-in that equips developers, data professionals, and DBAs with the unmatched functionality to improve their performance. The tool offers essential features that initially lack in the IDE: intelligent PL/SQL coding, Oracle PL/SQL debugger, DB change management, query profiler, etc.
Offering a set of extensive database management and administration tools that well integrate into the environment, dbForge Fusion for Oracle greatly elevates development teams productivity and lets its users go beyond the limits of the IDE.
dbForge Fusion for MySQL is a premier add-in that allows connecting and managing MySQL databases from Visual Studio. The tool is designed to push back the limits of the IDE by providing extra features: routines debugger, code completion and code formatting, query profiling, data import and export, visual query builder, and many more.
dbForge Fusion for MySQL empowers developers, DBAs, and data specialists to simplify the workflow, automate repetitive processes, reduce risks associated with changes, and significantly cut project delivery timelines.