Apr 15, 2019 
787.7k
27
DecChristmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL
- SQL Server Blogs -
SQL Server is a relational database management system marketed by Microsoft. Like other relational database management systems, SQL Server is also built on the top of SQL as a standard programming language and used to interact with the relational databases. SQL is tied to the T-SQL that has a proprietary programming construct.
SQL Server has worked exclusively for the Microsoft environment for more than 20 years now. In 2017, it was made available for both Windows and Linux platforms. Today, in this blog, we will discuss three commonly used operators of SQL Server; these are IN, Exists and JOIN clauses and various comparative studies such as SQL Join vs Inner Join, SQL Join vs where, etc.
Learn SQL Server in the Easiest Way
We will start the discussion with a brief introduction to these three operators, and then we will try to understand about the SQL join vs Union. T.
SQL Server uses many operators, and three of the most popular are Exists, IN and JOIN clauses. Let us discuss each of them one by one along with examples:
Exists is a logical SQL operator that helps to check the sub-query result, either True or False. It is used to check whether a row is returned through this sub-query or not? If one or more rows are returned, then this operator returns True otherwise False when no rows are returned. To check how to use Exists operator in SQL, Click here.
Syntax: Select column_name(s) From table_name Where Exists (Select column_name From table_name Where condition);Example: Let we have two tables for which we will use Exists operator:
| Product ID | Product Name | Unit | Price | Supplier ID |
| 1 | Chang | 24-12 oz bottles | $10 | 2 |
| 2 | Chais | 48-6 oz jars | $22 | 1 |
| 3 | Aniseed Syrup | 12-550 ml | $18 | 14 |
| 4 | Exotic Liquid | 36 boxes | $19 | 3 |
| 5 | Gumbo Box | 10 boxes | $21.5 | 15 |
| Supplier ID | Supplier Name | City | Postal Code |
| 1 | Tokyo Traders | London | 100 |
| 2 | Kelly’s Homestead | Tokyo | 48104 |
| 3 | Cajun Delight | New Orleans | 70117 |
| 4 | Exotic Liquid | Arbor | EC1 4SD |
Select Sr_Name From Supplier Where Exists (Select Pr_Name From Products Where Supplier_ID = Supplier.Supplier_ID And Price Here, the SQL statement result is True, and it returns a list of products whose price is less than 20.
You can specify multiple values through IN operator and use WHERE clause along. It is also known as the shorthand for multiple OR conditions. The syntax of IN operator in SQL Server is:
Read: Top 50 Datastage Interview Questions and Answers
Select column_name(s) From table_name Where column_name IN (value1, value2, - - - - );OR
Select column_name(s) From table_name Where column_name IN (Select Statement);For Example, the above tables for the IN query can be used as:
Select * From Product Where Price IN ($10, $22, $18);
The above query will return all the products whose costs are either $10, $22 or $18. One more example of IN query is given below using the Supplier table:
Select * From Supplier Where City NOT IN (London, Tokyo);Here, the result of the above query will be the name of all suppliers that are not living in London or Tokyo city.
SQL Server Training & Certification
JOIN SQL operation is used to establish connections between two or more tables of a database. This join is performed by matching the columns of the two tables. Many complex problems of databases are solved by JOIN operation. JOIN clause is used to combine the rows of two or more tables; for this, there should be a common column between both the tables. SQL Joins are of following types:
To check how SQL inner joins are different from SQL outer joins, click here. Three algorithms work behind these JOIN operations; these are hash join, nested join, and sort-join. The default JOIN type is INNER JOIN. In this JOIN, those records of two tables are selected whose values are matched. The rest of the records are excluded from the result. Below is the diagrammatic representation of these joins:
Let us apply the INNER JOIN clause on Product and Supplier tables to get the desired values of these tables. We can use the following syntax here: Select column_name(s) From table_name Inner Join table2_name On table_name.column_name = table2_name.column_name; Select Product.Product_ID, Supplier.Supplier_Name, Product.Price From Product Inner Join Suppliers.
To use and apply various types of JOINs, you must use the appropriate keywords in place of Inner Join.
The rule is applied as per the type of Join. Let us apply the Join on two tables that are product and supplier:
Select Product.Product_Id, Product.Price, Product.Pr_name Inner Join Product On Product.Supplier_iD== Supplier.Support_ID;
Here, the above query displays the Product id, price, and name of those products that have the same supplier_id. Moreover, here rows that have the same supplier id and the rest details will not be displayed.
Read: Top 30 Data Modeling Interview Questions with Answers
Many of the SQL Server users know the syntax of In, Join and Exists clauses. They know the working of In clause, but do not have a clear understanding of Exists and Join clause. We can make this difference clear by writing a similar query for all of these three clauses. For this, let us take the example of the following tables:
Table A Table B
| Id Name | Id Title |
| 1 Rob | |
| 2 Kenny | 1 Manager |
| 2 Anny | 2 Sales |
| 4 John | 3 Analyst |
| 3 Greg |
Here to know the name of the analysts, we can apply the following query:
Select * from tableA where tableA. Id IN (Select tableB.id From tableB where title=’Analyst’);Here, the output of this query will be just a single record that is Greg For those who are not familiar with the SQL syntax, it could be quite easier to understand this. Through this query, the IN clause is being used that will compare the values from each table and display all values from table A that have the same id as of title Analyst, i.e. 3. However, IN statement is quite clear but often it is found less efficient than Join and Exists clauses. The same result can be produced through the following queries as well:
Select * from tableA Where EXISTS (Select 1 From tableB Where title=’Analyst’ And tableA.id=tableb.id);Using JOINS (Inner Join is the default join when the name is not specified):
Select * from tableA JOIN tableB ON tableA.id=tableB.id Where tableB.title = ‘Analyst’;SQL Join vs Subquery and SQL Join vs Where
It has been seen that in several cases EXISTS and JOIN are much more efficient than IN clause. Well, you want to know why and how then we have listed it below:
When an IN clause is being used by combining it with a subquery, then the database will process the entire subquery result firstly and after that it will process the result of the query as a whole, as per the result of matching the query.
Read: SSIS Package - SSIS DB, Security and Upgrades
In case of Exists and Join clause, when such query will be executed the result of the query will be returned either True or False. For the large tables in the subquery, Exists and Join clauses perform well.
Moreover, Join also gives more flexibility to return all of the employees that either have or do not have the desired or specified title. Following query is used to display the title of employees:
Select * From tableA Join tableB ON tableA.id = tableB.id;The output of this query will be: 1 Rob 1 Manager 2 Kenny 2 Sales 2 Anny 2 Sales 3 Greg 3 Analyst To Display the Name of Employees that do not have any title, we can use the following query:
Read: What is the SQL Insert Query? How to Insert (Date, Multiple Rows, and Values in Table)
Select * From tableA LEFT JOIN tableB ON tableB ON tableA.id = tableB.id Where tableB.id IS NULL;The output of this query will be:
4 John NULL NULL Here, only John does not have any title. Moreover, if we want to enlist this value through JOIN query, then we can use LEFT JOIN clause instead of INNER JOIN, and it will be displayed with NULL data through this query.
If you have used lots of IN statements throughout your code, then you can compare the performance of IN clause with JOIN and EXISTS operators and then you can use it at the appropriate location. For those who still have the misconception that IN behaves same as EXISTS and JOIN in terms of the returned result, they can see here that this is simply not true and it depends on the use of clause. The one-liner interpretation of these subqueries looks like this:
Well, these statements may look quite similar, but in reality when they will be applied to the tables or the sub-queries, then the internal behavior of these statements is found quite different.
SQL Server Training & Certification
I hope this blog illustrating SQL join vs union, SQL join vs subquery etc was helpful to you. As we have seen in this blog that all the three clauses - JOIN, IN and EXISTS can be used for the same purpose, but they differ in their internal working. We can say that their logical working is different. If you are likely to attend an interview on SQL server related profile you must know about the SQL join vs union and the other related concepts such as SQL inner vs outer join, SQL join vs subquery, SQL jon vs where etc.
You can select any of them as per your requirement. Moreover, for the large tables, it is good to use either JOIN of EXISTS rather than IN. To learn more about operators, you can join an online learning platform too.
Read: Normalization in SQL | 1NF, 2NF, 3NF and BCNF with Examples
FaceBook
Twitter
LinkedIn
Pinterest
Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Jun 28, 2018
537.6k
537.6k