Kinh Nghiệm Hướng dẫn Which type of join is used to returns all from a table even when there are no matching rows? Mới Nhất
Bùi Bình Minh đang tìm kiếm từ khóa Which type of join is used to returns all from a table even when there are no matching rows? được Cập Nhật vào lúc : 2022-08-29 15:10:10 . Với phương châm chia sẻ Bí quyết Hướng dẫn trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi đọc tài liệu vẫn ko hiểu thì hoàn toàn có thể lại Comment ở cuối bài để Ad lý giải và hướng dẫn lại nha.Structured Query Language aka SQL is the core of relational databases with the help of which we can handle data. It provides us with various features such as Triggers, Injection, Hosting and, Joins is just one of the most important concept to master in SQL. In this article on SQL Joins, I will discuss the various types of Joins used in SQL.
Nội dung chính- What are Joins?How many types of Joins are there in SQL?How do I know which join to use in SQL?Employee Table:Project Table:Most Common Questions asked about JoinsQuestion 1: What is a Natural Join and in which situations is a natural join used?Question 2: How to map many-to-many relationships using joins?Question 3: What is a Hash Join?Question 4: What is Self & Cross Join?Question 5: Can you JOIN 3 tables in SQL?Which type of join is used to return rows that do not have?In what type of join rows are returned even when there are no matches through the join criteria on the second table?Which join returns all the rows from the right table even if there are no matches in the left table?Which type of join is used to returns all rows from left table and only matched rows from right table?
The following topics will be covered in this article
- What are Joins?How many types of Joins are there in SQL? How do I know which join to use in SQL
- INNER JOINFULL JOINLEFT JOINRIGHT JOIN
What are Joins?
JOINS in SQL are commands which are used to combine rows from two or more tables, based on a related column between those tables. There are predominantly used when a user is trying to extract data from tables which have one-to-many or many-to-many relationships between them.
Now, that you know what joins mean, let us next learn the different types of joins.
How many types of Joins are there in SQL?
There are mainly four types of joins that you need to understand. They are:
- INNER JOINFULL JOINLEFT JOINRIGHT JOIN
You can refer to the below image.
How do I know which join to use in SQL?
Let us look into each one of them. For your better understanding of this concept, I will be considering the following three tables to show you how to perform the Join operations on such tables.
Employee Table:
EmpID EmpFname EmpLname Age EmailID PhoneNo Address 1 Vardhan Kumar 22 9876543210 Delhi 2 Himani Sharma 32 9977554422 Mumbai 3 Aayushi Shreshth 24 9977555121 Kolkata 4 Hemanth Sharma 25 9876545666 Bengaluru 5 Swatee Kapoor 26 9544567777 HyderabadProject Table:
ProjectID EmpID ClientID ProjectName ProjectStartDate 111 1 3 Project1 2022-04-21 222 2 1 Project2 2022-02-12 333 3 5 Project3 2022-01-10 444 3 2 Project4 2022-04-16 555 5 4 Project5 2022-05-23 666 9 1 Project6 2022-01-12 777 7 2 Project7 2022-07-25 888 8 3 Project8 2022-08-20Client Table:
ClientID ClientFname ClientLname Age ClientEmailID PhoneNo Address EmpID 1 Susan Smith 30 9765411231 Kolkata 3 2 Mois Ali 27 9876543561 Kolkata 3 3 Soma Paul 22 9966332211 Delhi 1 4 Zainab Daginawala 40 9955884422 Hyderabad 5 5 Bhaskar Reddy 32 9636963269 Mumbai 2INNER JOIN
This type of join returns those records which have matching values in both tables. So, if you perform an INNER join operation between the Employee table and the Projects table, all the tuples which have matching values in both the tables will be given as output.
Syntax:SELECT Table1.Column1,Table1.Column2,Table2.Column1,.... FROM Table1 INNER JOIN Table2 ON Table1.MatchingColumnName = Table2.MatchingColumnName;NOTE: You can either use the keyword INNER JOIN or JOIN to perform this operation.
Example:SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName FROM Employee INNER JOIN Projects ON Employee.EmpID=Projects.EmpID; Output: EmpID EmpFname EmpLname ProjectID ProjectName 1 Vardhan Kumar 111 Project1 2 Himani Sharma 222 Project2 3 Aayushi Shreshth 333 Project3 3 Aayushi Shreshth 444 Project4 5 Swatee Kapoor 555 Project5FULL JOIN
Full Join or the Full Outer Join returns all those records which either have a match in the left(Table1) or the right(Table2) table.
Syntax:SELECT Table1.Column1,Table1.Column2,Table2.Column1,.... FROM Table1 FULL JOIN Table2 ON Table1.MatchingColumnName = Table2.MatchingColumnName;Example:SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID FROM Employee FULL JOIN Projects ON Employee.EmpID = Projects.EmpID; Output: EmpFname EmpLname ProjectID Vardhan Kumar 111 Himani Sharma 222 Aayushi Shreshth 333 Aayushi Shreshth 444 Hemanth Sharma NULL Swatee Kapoor 555 NULL NULL 666 NULL NULL 777 NULL NULL 888LEFT JOIN
The LEFT JOIN or the LEFT OUTER JOIN returns all the records from the left table and also those records which satisfy a condition from the right table. Also, for the records having no matching values in the right table, the output or the result-set will contain the NULL values.
Syntax:SELECT Table1.Column1,Table1.Column2,Table2.Column1,.... FROM Table1 LEFT JOIN Table2 ON Table1.MatchingColumnName = Table2.MatchingColumnName;Example:SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName FROM Employee LEFT JOIN ON Employee.EmpID = Projects.EmpID ; Output: EmpFname EmpLname ProjectID ProjectName Vardhan Kumar 111 Project1 Himani Sharma 222 Project2 Aayushi Shreshth 333 Project3 Aayushi Shreshth 444 Project4 Swatee Kapoor 555 Project5 Hemanth Sharma NULL NULLRIGHT JOIN
The RIGHT JOIN or the RIGHT OUTER JOIN returns all the records from the right table and also those records which satisfy a condition from the left table. Also, for the records having no matching values in the left table, the output or the result-set will contain the NULL values.
Syntax:SELECT Table1.Column1,Table1.Column2,Table2.Column1,.... FROM Table1 RIGHT JOIN Table2 ON Table1.MatchingColumnName = Table2.MatchingColumnName;Example:SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName FROM Employee RIGHT JOIN ON Employee.EmpID = Projects.EmpID; Output: EmpFname EmpLname ProjectID ProjectName Vardhan Kumar 111 Project1 Himani Sharma 222 Project2 Aayushi Shreshth 333 Project3 Aayushi Shreshth 444 Project4 Swatee Kapoor 555 Project5 NULL NULL 666 Project6 NULL NULL 777 Project7 NULL NULL 888 Project8Now, let us move forward with our next section in this article i.e. the top questions asked about SQL Joins in your interviews.
Most Common Questions asked about Joins
Question 1: What is a Natural Join and in which situations is a natural join used?
Solution:
A Natural Join is also a Join operation that is used to give you an output based on the columns in both the tables between which, this join operation must be implemented. To understand the situations n which natural join is used, you need to understand the difference between Natural Join and Inner Join.
The main difference the Natural Join and the Inner Join relies on the number of columns returned. Refer below for example.
Now, if you apply INNER JOIN on these 2 tables, you will see an output as below:
If you apply NATURAL JOIN, on the above two tables, the output will be as below:
From the above example, you can clearly see that
the number of columns returned from the Inner Join is more than that of the number of columns returned from Natural Join. So, if you wish to get an output, with less number of columns, then you can use Natural JoinQuestion 2: How to map many-to-many relationships using joins?
Solution:
To map many to many relationships using joins, you need to use two JOIN statements.
For example, if we have three tables(Employees, Projects and Technologies), and let us assume that each employee is working on a single project. So, one project cannot be assigned to more than one employee. So, this is basically, a one-to-many relationship.
Now, similarly, if you consider that, a project can be based on multiple technologies, and any technology can be used in multiple projects, then this kind of relationship is a many-to-many relationship.
To use joins for such relationships, you need to structure your database with 2 foreign keys. So, to do that, you have to create the following 3 tables:
- ProjectsTechnologiesprojects_to_technologies
The project_to_technologies table holds the combinations of project-technology in every row. This table maps the items on the projects table to the items on the technologies table so that multiple projects can be assigned to one or more technologies.
Once the tables are created, use the following two JOIN statements to link all the above tables together:
- projects_to_technologies to projectsproejcts_to-technologies to technologies
Question 3: What is a Hash Join?
Solution:
Hash joins are also a type of joins which are used to join large tables or in an instance where the user wants most of the joined table rows.
The Hash Join algorithm is a two-step algorithm. Refer below for the steps:
- Build phase: Create an in-memory hash index on the left side inputProbe phase: Go through the right side input, each row a time to find the matches using the index created in the above step.
Question 4: What is Self & Cross Join?
Solution:
Self JoinSELF JOIN in other words is a join of a table to itself. This implies that each row in a table is joined with itself.
Cross JoinThe CROSS JOIN is a type of join in which a join clause is applied to each row of a table to every row of the other table. Also, when the WHERE condition is used, this type of JOIN behaves as an INNER JOIN, and when the WHERE condition is not present, it behaves like a CARTESIAN product.
Question 5: Can you JOIN 3 tables in SQL?
Solution:
Yes. To perform a JOIN operation on 3 tables, you need to use 2 JOIN statements. You can refer to the second question for an understanding of how to join 3 tables with an example.
NOTE: To apply a JOIN operation between ‘n‘ tables, you have to use ‘n-1‘ JOIN statements.
Now that you know SQL Joins, I’m sure you’re curious to learn more about SQL. Here’s a list of articles that you can refer to:
SQL CommandsSQL Data TypesSpark SQLSQL Interview QuestionsWhat is MYSQL?By this, I come to the end of this article on SQL Joins. I hope you enjoyed reading this article on SQL Joins. We have seen the different commands that will help you write queries and play around with your databases. If you wish to learn more about MySQL and get to know this open source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of ”SQL Joins” and I will get back to you.
Which type of join is used to return rows that do not have?
1. What type of join is needed when you wish to include rows that do not have matching values? Explanation:OUTER JOIN is the only join which shows the unmatched rows.In what type of join rows are returned even when there are no matches through the join criteria on the second table?
LEFT JOIN is used; this will return ALL rows from Table1 , regardless of whether or not there is a matching row in Table2 .Which join returns all the rows from the right table even if there are no matches in the left table?
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.Which type of join is used to returns all rows from left table and only matched rows from right table?
The LEFT JOIN is a clause of the SELECT statement. The LEFT JOIN clause allows you to query data from multiple tables. The LEFT JOIN returns all rows from the left table and the matching rows from the right table. Tải thêm tài liệu liên quan đến nội dung bài viết Which type of join is used to returns all from a table even when there are no matching rows?