Show
SQL JOIN is a clause that is used to combine multiple tables and retrieve data based on a common field in relational databases. Database professionals use normalizations for ensuring and improving data integrity. In the various normalization forms, data is distributed into multiple logical tables. These tables use referential constraints – primary key and foreign keys – to enforce data integrity in SQL Server tables. In the below image, we get a glimpse of the database normalization process. Understanding the different SQL JOIN typesSQL JOIN generates meaningful data by combining multiple relational tables. These tables are related using a key and have one-to-one or one-to-many relationships. To retrieve the correct data, you must know the data requirements and correct join mechanisms. SQL Server supports multiple joins and each method has a specific way to retrieve data from multiple tables. The below image specifies the supported SQL Server joins. SQL inner joinThe SQL inner join includes rows from the tables where the join conditions are satisfied. For example, in the below Venn diagram, inner join returns the matching rows from Table A and Table B. In the below example, notice the following things:
The query output returns the employee records for EmpID that exists in both tables. The inner join returns matching rows from both tables; therefore, it is also known as Equi join. If we don’t specify the inner keyword, SQL Server performs the inner join operation. In another type of inner join, a theta join, we do not use the equality operator (=) in the ON clause. Instead, we use non-equality operators such as < and >. SELECT * FROM Table1 T1, Table2 T2 WHERE T1.Price < T2.price SQL self-joinIn a self-join, SQL Server joins the table with itself. This means the table name appears twice in the from clause. Below, we have a table [Emp] that has employees as well as their managers’ data. The self-join is useful for querying hierarchical data. For example, in the employee table, we can use self-join to learn each employee and their reporting manager’s name. The above query puts a self-join on [Emp] table. It joins the EmpMgrID column with the EmpID column and returns the matching rows. SQL cross joinIn the cross join, SQL Server returns a Cartesian product from both tables. For example, in the below image, we performed a cross-join for table A and B. The cross join joins each row from table A to every row available in table B. Therefore, the output is also known as a Cartesian product of both tables. In the below image, note the following:
In the cross-join output, row 1 of [Employee] table joins with all rows of [Address] table and follows the same pattern for the remaining rows. If the first table has x number of rows and the second table has n number of rows, cross join gives x*n number of rows in the output. You should avoid cross join on larger tables because it might return a vast number of records and SQL Server requires a lot of computing power (CPU, memory and IO) for handling such extensive data. SQL outer joinAs we explained earlier, the inner join returns the matching rows from both of the tables. When using a SQL outer join, it not only lists the matching rows, but it also returns the unmatched rows from the other tables. The unmatched row depends on the left, right or full keywords. The below image describes at a high-level the left, right and full outer join. Left outer joinSQL left outer join returns the matching rows of both tables along with the unmatched rows from the left table. If a record from the left table doesn’t have any matched rows in the right table, it displays the record with NULL values. In the below example, the left outer join returns the following rows:
Right outer joinSQL right outer join returns the matching rows of both tables along with the unmatched rows from the right table. If a record from the right table does not have any matched rows in the left table, it displays the record with NULL values. In the below example, we have the following output rows:
Full outer joinA full outer join returns the following rows in the output:
SQL joins with multiple tablesIn the previous examples, we use two tables in a SQL query to perform join operations. Mostly, we join multiple tables together and it returns the relevant data. The below query uses multiple inner joins. USE [AdventureWorks2019] GO SELECT e.[BusinessEntityID] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,e.[JobTitle] ,d.[Name] AS [Department] ,d.[GroupName] ,edh.[StartDate] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[BusinessEntityID] = edh.[BusinessEntityID] INNER JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID] WHERE edh.EndDate IS NULL GO Let’s analyze the query in the following steps:
Once you execute the query with multiple joins, query optimizer prepares the execution plan. It prepares a cost-optimized execution plan satisfying the join conditions with resource usage—for example, in the below actual execution plan, we can look at multiple nested loops (inner join) and hash match (inner join) combining data from multiple joining tables. NULL values and SQL joinsSuppose we have NULL values in the table columns, and we join the tables on those columns. Does SQL Server match NULL values? The NULL values do not match one another. Therefore, SQL Server could not return the matching row. In the below example, we have NULL in the EmpID column of the [Employees] table. Therefore, in the output, it returns the matching row for [EmpID] 2 only. We can get this NULL row in the output in the event of a SQL outer join because it returns the unmatched rows as well. SQL join best practicesIn this article, we explored the different SQL join types. Here are a few important best practices to remember and apply when using SQL joins.
Which join will combine rows from different tables if the join condition is true Mcq?Explanation: An SQL join clause combines records from two or more tables in a database.
Which join will combine rows from different tables?Cartesian Join also known as the Cross Join, returns the cartesian product of the tables being joined, which is the combination of every row of one table with every row of another table.
Which join can be used to join entries from two tables so that each row in each table has a match options Inner?The simplest kind of join we can do is a CROSS JOIN or "Cartesian product." This join takes each row from one table and joins it with each row of the other table. Each value from the first list is paired with each value of the second list.
Which type of join is used to returns all rows if there is one match in both tables?The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
|