Welcome To TechBrothersIT: 2016

How to check if table exists in all databases in SQL Server- SQL Server Scripts

Scenario:

You are working as SQL Server DBA / Developer. You have many databases on SQL Server Instance and you would like to check in which databases table exists or does not exists.

Declare @TableName VARCHAR(128)
--Provide the Table Name that you would like to Check
SET @TableName='MyTestTable'
    
    --Drop Temp table if exists to save record 
    IF OBJECT_ID('tempdb..##RecordCount') IS NOT NULL
        DROP TABLE ##RecordCount

    CREATE TABLE ##RecordCount (
        ServerName VARCHAR(128),
        DatabaseName VARCHAR(128)
        ,TableName VARCHAR(128)
        ,IsExists VARCHAR(10)
        )

    --Use Cursor to Loop through Databases
    DECLARE @DatabaseName AS VARCHAR(500)

    DECLARE CDCCursor CURSOR
    FOR
    SELECT NAME
    FROM sys.databases
    where database_id>4

    OPEN CDCCursor
    FETCH NEXT
    FROM CDCCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');
        Print @DbName
        --USE Dynamic SQL To change the Database Name 
        EXEC (
N'USE ' + @DBName + N'; EXEC(''
Insert into ##RecordCount
Select @@ServerName,DB_Name() AS DatabaseName,
'''''+@TableName+''''',(Select CASE WHEN count(*)>1 
then ''''YES'''' ELSE ''''NO'''' END AS IsExist 
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='''''+@TableName+'''''
and Table_Type=''''BASE TABLE'''')
'');'
                );

        FETCH NEXT
        FROM CDCCursor
        INTO @DatabaseName
    END

    CLOSE CDCCursor

    DEALLOCATE CDCCursor

    --Return if Table Exists
    SELECT *
    FROM ##RecordCount

How to find if Table exists in all the databases in SQL Server

How to get Maximum and Minimum Length for each Column Values for all the tables in SQL Server Database

Scenario:

Let's say you are working as SQL Server developer / Architect and you need to design tables from existing tables. While creating existing tables nobody thought about correct data types and used NVARCHAR(MAX) or VARCHAR(MAX) , text or even ntext. Your goal is analyse the data and then come up best data types for each column. While analysis you need to know the max length for the values your columns so you can proviude max data type length for new table columns.

The below script is going to run on entire database and get the maximum and minimum length from each of the column for each of the table and return you at the end. This query can take quite a long time depending upon the number of tables, columns and records.

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
DECLARE @DataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,MaxLength VARCHAR(50)
    ,MinLength VARCHAR(50)
    )

DECLARE Cur CURSOR
FOR
SELECT DB_Name() AS DatabaseName
    ,s.[name] AS SchemaName
    ,t.[name] AS TableName
    ,c.[name] AS ColumnName
    ,'[' + DB_Name() + ']' + '.[' + s.NAME + '].' + '[' + T.NAME + ']' AS FullQualifiedTableName
    ,d.[name] AS DataType
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE d.NAME LIKE '%char%'
or d.name like '%Text%'


OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' + @SchemaName + ''' AS SchemaName,
      ''' + @TableName + ''' AS TableName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DataType + ''' AS ColumnDataType,
      (Select MAX(LEN(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)))) from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS MaxLength,
      (Select MIN(LEN(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)))) from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS MinLength'

    PRINT @SQL

    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END

CLOSE Cur

DEALLOCATE Cur

SELECT *
FROM #Results

How to get max and min column value length for all the columns in SQL Server Database

How to create SSRS Report Dynamically with Dynamic Schema, Dynamic Table Name and Dynamic Columns Part 59.1

Scenario:

Few months back, I created a post/video "How to Create SSRS Report When Number of Columns Can change Anytime in Source Object". In this post we are going to create another dynamic reports in which we will be able to

1) Choose the Schema form our Database
2) Choose Table for given schema from step 1
3) Choose column/s depending upon values of step 1 and Step 2
4) Display the data on report for our selected columns

This type of report can be very helpful when we have to simply create many details reports for users from our database. If we would like we can add also the Where clause in our report to provide filtered reports. Let's leave that challenge to you.

SQL Queries for our Data Sets

1) DS_Report
Link to Code

3) DS_Tables
This will return the list of user tables depending upon the schema you will choose

Select Distinct Table_Name as TableName from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
and TABLE_SCHEMA=@SchemaName
order by Table_Name

4) DS_Columns
This will return us the list of columns depending upon the selection of Schema and Table

Select COLUMN_NAME as ColumnName from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA=@SchemaName
and TABLE_NAME=@TableName

How to download File from SharePoint and Add Datetime to it in SSIS Package by using Script Task - SSIS Tutorial / SSIS Interview Questions

Scenario:

You are working as ETL/ SSIS Developer. You need to write an SSIS Package that should download the file from SharePoint site. Every time you run the SSIS Package it should download the file and add date_time to it.

Solution:

We will be using HTTP Connection and Script Task to download the file from SharePoint.

Step1 :

Create your SSIS Package by using BIDS or SSDT ( SQL Server Data Tools).

Step 2:

Create Three variables of string type

FolderPath :

Provide the folder in which you want to download the file.

FileName :

Provide the destination file name with extension such as myfile.xlsx or mytestfile.doc

FileFullPath :

This is the variable we will be using to write expressions on it. Click on the expressions tab and then write the below expressions.

How to download file from SharePoint and Add Datetime to it in SSIS Package

Once you click on the Expression Button as shown above, paste the below expressions.

@[User::FolderPath]+ Replace( @[User::FileName],".", "_"+Replace(Replace(Replace(SUBSTRING((DT_WSTR,50)(GETDATE()),1,19),"-","")," ","_"),":","")+".")

Write expressions on variable in SSIS Package to add Date_Time to it

Step 2:
To create the HTTP Connection to SharePoint File. Go to Connection Managers Pane and then right click and then choose New Connection.

How to create HTTP Connection Manager to SharePoint to download File in SSIS Package

How to download file from SharePoint by using SSIS Package by using Script Task with HTTP Connection

How to create Connection Manager to SharePoint in SSIS Package

To get the Server URL ( Path to file on SharePoint). You can go to SharePoint and then go to File and Right Click, Choose Properties and then you will see Address (URL) that is the Server URL you need to use in Connection above. Also provide the user name and password which has permission on the SharePoint File. Test the connection and it should be successful.

A new connection should be created once you click OK. You can rename if you like,I am going to leave this as it is.

Step 4:
Bring the Script task in Control Flow Pane and open, you need to add the variable.

How to use Script Task to download File from SharePoint in SSIS Package

Step 5:
Click on Edit Script Button and then paste below script under public void Main() {

   string FilePath = Dts.Variables["User::FileFullPath"].Value.ToString();
   object obj = Dts.Connections["HTTP Connection Manager"].AcquireConnection(null);
   HttpClientConnection HTTPConnection = new HttpClientConnection(obj);
   HTTPConnection.DownloadFile(FilePath, true);

Save the script and close the Editor windows. Run your SSIS Package, It should download the file and add date_time to file name. Every time you will run the SSIS Package , the new file will be downloaded and date_time will be added to it.

I executed the SSIS Package couple of times and you can see that the file is downloaded and date_time is added to it.

How to download files from SharePoint and add Date_Time on each execution in SSIS Package

How to download file from SharePoint by using SSIS Package - SSIS Tutorial / SSIS Interview questions

Scenario:

You are working as SSIS Developer and you need to download the file from SharePoint on daily basis, if the file exists in the folder you want to overwrite with new file. The file name that you need to download from the SharePoint stays the same.

Solution:

We will be using the Script Task and HTTP connection Manager in SSIS Package to download the file and write to folder in SSIS Package.

Step 1:

Create a new SSIS Package by using BIDS or SSDT ( SQL Server Data Tools).

Step 2:

Create a variable called FilePath where you would like to save the file on local folder.

How to download File from SharePoint and Save to Local Folder in SSIS Package by using Script Task

Step 3:
Create the HTTP Connection to SharePoint File. Go to Connection Managers Pane and then right click and then choose New Connection.

How to create HTTP Connection Manager to SharePoint to download File in SSIS Package

How to download file from SharePoint by using SSIS Package by using Script Task with HTTP Connection

How to create Connection Manager to SharePoint in SSIS Package

Step 4:
Bring the Script task in Control Flow Pane and open, you need to add the variable.

How to download file from SharePoint to Local Folder by using Script Task in SSIS Package

Click on Edit Script Button and then paste below script under public void Main() {

   string FilePath = Dts.Variables["User::FilePath"].Value.ToString();
   object obj = Dts.Connections["HTTP Connection Manager"].AcquireConnection(null);
   HttpClientConnection HTTPConnection = new HttpClientConnection(obj);
   HTTPConnection.DownloadFile(FilePath, true);

Save the script and close the Editor windows. Run your SSIS Package, it should download the file. Every time you will execute the SSIS Package, it will overwrite the existing file from SharePoint file.

How to Run Stored Procedure Against Multiple Databases in SQL Server - SQL Server / TSQL Scripts

Scenario:

You are working as SQL Server developer. The company you are working create a new database for each of the client with same objects such as tables/Stored Procedures. You have one stored procedure with the same name let's say dbo.LoadCustomer present in all the databases. You need to execute that Stored Procedure from multiple databases. The script can be used one time or you can also run by using SQL Server agent if has to run on schedule.

Solution:

We can use Cursor to loop through list of the databases in which our Stored Procedure is present and we want to execute. Below is sample script. Please change the @SPName and filter the list of database as per your requirements.

--Use Cursor to Loop through Databases
DECLARE @DatabaseName AS varchar(500)
--Provide the name of SP that you want to run
DECLARE @SPName AS varchar(128) = 'dbo.loadCustomer'

DECLARE DBCursor CURSOR FOR
--Filter the list of the database in which Stored Procedure exists
SELECT
  NAME
FROM sys.databases
WHERE database_id > 4

OPEN DBCursor

FETCH NEXT
FROM DBCursor
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @DBName AS nvarchar(500);

  SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

  --USE Dynamic SQL To change the Database Name and 
  --Execute Stored Procedure from that Database
  EXEC (
  N'USE ' + @DBName + N'; EXEC(''' + @SPName + '
'');'
  );

  FETCH NEXT
  FROM DBCursor
  INTO @DatabaseName
END

CLOSE DBCursor

DEALLOCATE DBCursor

How to see Created Date for Files and Folders in Windows Explorer

When we often Windows Explorer we see different properties and one of them we see for file and folder is Modified Date.

Sometime we need to know when the file or folder was created and we would like to see Date Created. To add that Right Click right next to Size or any other property as shown and then click on Created On as shown below.

How to show Created Date for file and folders in Windows Explorer

The Date Created should be added to list now.

How to see Created Date for Folders and Files in windows explorer

How to delete all files except current date files in SSIS Package - SSIS Tutorial

Scenario:

You are working as ETL Developer or an SSIS developer, you need to write an SSIS Package that should be able to delete all the old files from a folder and just leave the current day files.

How to Delete all files from a folder except Files created today in SSIS Package by using Script Task

Solution:

We can get the Date Created for the file and if it is created today we will not delete , otherwise we will delete the file.

Step 1: Create an SSIS Package by using SSDT

Step 2: Create a variable called SourceFolder and provide the path for folder in which your files exists.

How to drop all old files from folder just leaving for current day files in SSIS Package by using Script Task

Step 3:
Bring the script Task to the Control Flow pane and add SourceFolder variable to it as shown below.

How to drop old files from a folder by using SSIS Script Task except Current Day files

Step 4 :
Click on Edit Script and then use the page the below script.

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

using System.IO;

Go to public void Main() and Paste below Code right under // TODO: Add your code here

           var directory = new DirectoryInfo(Dts.Variables["User::SourceFolder"].Value.ToString());
            FileInfo[] files = directory.GetFiles();
            
            foreach (FileInfo file in files)
            {
                if (file.CreationTime < DateTime.Today)
                {
                    //MessageBox.Show(file.Name);
                   file.Delete();
                }
            }

Step 5:
Save the script and then close the Editor window. Your SSIS Package is ready. Execute and it should delete all the old files from a folder and leave only current day files. Noticed, my SSIS Package has deleted all the files and I am left with only current day files in my folder.

Delete all files except current day files in SSIS Package by using Script Task

Project / Work Support

Looking for some support who can help with difficult task/s and be there when you need them, TechBrothers have got your back!

OR

Have small or big project that needs to be finished by professionals on time, we are here to help.

We provide following services

SQL Server Developement
SQL Server Adminstration
MSBI ( SSIS, SSRS,SSAS )
Team Foundation Server (TFS)
Setting up Windows Clusters
MS Dynamix
Data Warehouse

Either you need monthly/ yearly support or one time. Try TechBrothersIT one time!

To discuss your Project / Work details and Rate. Please contact us

Email : aamirsqlage@gmail.com

Phone : 505-414-1969

What Arithmetic Operators are available in SQL Server - SQL Server / TSQL Part 129

Arithmetic Operators are used to perform mathematical operations such as Addition, Subtraction, Multiplication and Division etc.

1 ) + will be used for Addition
2) - will be used for Subtraction
3) * will be used for Multiplication
4) / will be used for Division
5) % will be used for Modulo ( Returns the integer remainder of a division)

Let's write our query to perform these operations

Select 
2+3 as Addition,
5-2 as Subtraction,
5*6 as Multiplication,
40/5 as Division,
6/4 as IntDivision,
6/4.0 as NumericDivision,
7%2 as Modulos

How to perform Arithmetic Operations in SQL Server - SQL Server / TSQL Tutorial

Noticed that when you divided 6/4 both integers, the output returned will be integer. In our case it returned 1. To get your output in decimals ,At least one of dividend or divisor need to be numeric(decimal).

I used static values for arithmetic operations. you can multiple columns, make sure they are numeric data types.

Addition ( + ) can also be used to concatenate the string in TSQL.

Select 'Aamir ' + 'Shahzad' as MyName

How to use Addition Sign to perform Concatenation in SQL Server - SQL Server / TSQL Tutorial

The Addition and Subtraction signs can also be used to perform arithmetic operations on datetime and smalldatetime values.

Let's say that if I would like to add or subtract few days from Current date, I can use below query.

Select GETDATE()+5 AS Add5eDaystoCurrentDate,
GETDATE()-30 AS Subtract30DaysfromCurrentDate

How to use Assignment Operator in SQL Server - SQL Server / TSQL Tutorial Part 128

The equal ( = ) sign is used in TSQL as assignment operator. You will be using assignment operator many time when you write your TSQL Code.

1) Assign Value to Variable
Assignment Operator can be used to set the value for variable. You can simply assign static value to variable by using Assignment Operator or value returned by query.

Declare @i int
SET @i=2000
Print @i

Declare @Cnt int
SET @Cnt=(Select COUNT(*) from dbo.Customer)
Print @Cnt

Declare @CntRow int
Select @CntRow=Count(*) from dbo.Customer
Print @CntRow

2) Let's say you have dbo.Customer table and you want to add a static value column, You can use Assignment Operator to assign value to newly added column. In below example I am adding Region column by assigning 'North America'.

--Create Customer Table
Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87)

Also we can use the assignment operator for Alias. I have used CustomerID alias for ID column by using assignment operator.

Select 
CustomerId=Id,
FName,
LName,
CountryShortName,
Age,Region='North America' 
From dbo.Customer

What is Assignment Operator in SQL Server and How to use Assignment Operator - SQL Server / TSQL Tutorial

How to use ANY / SOME Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 127

ANY Logical operator returns TRUE if any one of a set of comparisons are TRUE. ANY compares a scalar value with a single column set of values.

Note : SOME and ANY are equivalent. We are going to use ANY in our below examples.

Scenario :

Let's say that we have two tables dbo.Customer and dbo.Customer1. Both tables has the column Age. If you need to get all the records from dbo.Customer table where Age is at-least greater than one value from Age column from dbo.Customer1 table.

Solution:

We can use subquery and MIN function to write our query for above requirement. Let's create the tables first.

--Create Customer Table
Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87)

--Create dbo.Customer1 table
Create table dbo.Customer1
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
--Insert rows in dbo.Customer1 Table
insert into dbo.Customer1
Values
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),
(9,'Adita','M','US',29)

1) Get all the records from dbo.Customer table where Age is greater than min Age value of dbo.Customer1 table by using Subquery and Min function.

Select * From dbo.Customer
where Age> ( Select MIN(age) from dbo.Customer1)

2) Use ANY to get required results.
We can use ANY instead of using Min function with subquery. As we want to get all rows from dbo.Customer where Age is greater than any value of Age column in dbo.Customer, We will use >Any.
>ANY means greater than at least one value, that is, greater than the minimum.

Select * From dbo.Customer
where Age>ANY ( Select age from dbo.Customer1)

We got the same records what were returned by our first query.
If you will use =ANY that is equal to IN. With ANY you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !<

Video Demo : How to use ANY / SOME Logical Operator in SQL Server

How to use ALL Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 126

ALL Logical operator returns TRUE if all of a set of comparisons are TRUE. ALL compares a scalar value with a single column set of values.

Let's understand ALL with examples.

Scenario :

Think about a scenario where we have two tables dbo.Customer and dbo.Customer1. Both tables has the column Age. If you need to get all the records from dbo.Customer table where Age is greater than maximum value of Age column in dbo.Customer1 table.What would be your query.

Solution:

We can use subquery and max function to write our query for above requirement. Let's create the tables first.

--Create Customer Table
Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87)

--Create dbo.Customer1 table
Create table dbo.Customer1
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
--Insert rows in dbo.Customer1 Table
insert into dbo.Customer1
Values
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),
(9,'Adita','M','US',29)

1) Get all the records from dbo.Customer table where Age is greater than maximum Age value of dbo.Customer1 table by using Subquery and Max function.

2) using ALL with SubQuery
For above requirement we can use ALL logical operator. In that case we don't have to use Max function. ALL is going compare our outer query value to set of values from subquery. We can use >All, >ALL means greater than every value returned by subquery, In other words greater than max value.

Select * From dbo.Customer
where Age> All ( Select age from dbo.Customer1)

How to use ALL Logical Operator in SQL Server - SQL Server / TSQL Tutorial

With ALL you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !<

Video Demo : How to use ALL Logical Operator in SQL / TSQL

How to use EXISTS Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 125

Exists returns TRUE if a subquery contains any rows. EXISTS is used when we want to test for the existence of rows specified by a subquery.

Let's create dbo.Customer and dbo.Country Table and then use EXISTS to return records for different scenarios.

Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),
(9,'Adita','M','US',29)


Create table dbo.Country ( 
CId tinyint,
CountryName VARCHAR(50),
CountryShortName CHAR(2))
go
Insert into dbo.Country 
Values 
(1,'Pakistan','Pk'),
(2,'United States of America','US')

1) EXISTS will return TRUE if subquery contains any rows.

That is right, if our subquery will return any rows and we have used EXISTS, the outer query is going to return all rows.

Select * from dbo.Customer

WHERE Exists ( Select 1)

How to use EXISTS in SQL Server - SQL Server / TSQL Tutorial

Noticed that our subquery is static query ( Select 1). As subquery did return row and EXISTS returned TRUE so all the records from dbo.Customer table are displayed.

2) Use EXISTS and Join with SubQuery
The more real time example of EXISTS would be when we want to find all the records from dbo.Customer table which has matching CountryShortName from dbo.Country Table.

SELECT *
FROM dbo.Customer a
WHERE EXISTS
    (SELECT 1
     FROM dbo.Country b
     WHERE a.CountryShortName=b.CountryShortName)

Noticed that I have compared CountryShortName from dbo.Customer and dbo.Country. Each outer row is going to be compared with subquery results and if matches , then we get the row.

We can use the IN clause for same requirement.

SELECT *
FROM dbo.Customer a
WHERE a.CountryShortName IN
    (SELECT b.CountryShortName
     FROM dbo.Country b
     WHERE a.CountryShortName=b.CountryShortName)

How to use EXISTS in SQL Server to return matching records - SQL Server / TSQL Tutorial

How to use Exists and Not Exits in SQL Server

Label

Scenario:

Scenario:

Scenario:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario :

Solution:

Scenario :

Solution: