SQL Join Multiple Tables With Conditions WHERE Clause
Summary: In this article, we learn SQL Join Multiple Tables With Conditions means that SQL multiple joins concept.
In our relational databases, data is stored in tables. here, in the SQL Join Multiple Tables With Conditions, we required a result set that is formed by combining data from several tables. Therefore, The SQL joins allow us to combine data from two or more tables thus, we are able to join data from the tables.
Furthermore, in order that we can easily retrieve data from multiple tables. Let’s discuss that how many different types of join exist in SQL Server. Basically, there are four main types of joins that exist in SQL Server.
we are going to shortly describe them using Venn diagram illustrations:
Types Of SQL Join Multiple Tables With Conditions Venn Diagram And Syntax
1) INNER JOIN:
The Inner Join SQL returns the rows that match in both tables (A&B)
|
1 2 3 4 |
SELECT <Select_List> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key |
2) LEFT JOIN:
This SQL Left Join will returns all rows from the left table
|
1 2 3 4 |
SELECT <Select_List> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key |
|
1 2 3 4 5 |
SELECT <Select_List> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL |
3) RIGHT JOIN:
This Right join SQL will returns all rows from the right table
|
1 2 3 4 |
SELECT <Select_List> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key |
|
1 2 3 4 5 |
SELECT <Select_List> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL |
4) FULL OUTER JOIN:
iN sql, the Full join will returns whole rows from both tables.
|
1 2 3 4 |
SELECT <Select_List> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key |
|
1 2 3 4 5 6 |
SELECT <Select_List> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL |
What Are SQL Multiple Joins?
Multiple joins in SQL play the most important role, SQL multiple joins is a query that contains identical or different join types, such as Inner Join, Left Join, Right Join, Outer Join. which we can be used more than once. Furthermore, with the help of these joins, we are able to combine multiple tables or more than one table of data in order to overcome relational database issues.
Two-Table joins in SQL
The given below following example shows a two-table join in SQL:
|
1 2 |
SELECT order_number, lastname, firstname FROM consumers, orders WHERE consumers.customer_num = orders.customer_num; |
Multi-Table Joins in SQL
A multiple-table join means its joins more than two tables. thus, its structure is also identical to the structure of a two-table join as seen in the above, except that you have a join condition for more than one pair of tables in the WHERE clause. Furthermore, when columns from different tables have the same name, then you must qualify the column name with its associated table or table alias, as in the table.column.
SQL Join Multiple Tables With Conditions:- therefore, the following multiple-table join yields the firm name of the consumers who ordered an item as well as its stock number & manufacturer.
|
1 2 3 4 |
SELECT DISTINCT firm, stock_number, menu_code FROM consumer c, orders o, items i WHERE c.consumer_num = o.customer_num AND o.order_num = i.order_num; |
Self Joins
You can join a table to itself. To do so, you need to list the table name doubly within the FROM clause and assign it to 2 completely different table aliases. Here, we will use the aliases to refer to every one of the two tables in the WHERE clause. Another example is a self-join on the stock table. Therefore, it basically finds the pairs of stock items whose unit prices differ by a factor greater than 3.7. The letters x and y are each alias for the stock table.
|
1 2 |
SELECT x.stock_number, x.manu_code, y.stock_number, y.menu_code FROM stock x, stock y WHERE x.unit_price > 3.7 * y.unit_price; |
SQL Join Multiple Tables With Conditions
|
1 2 3 4 5 6 7 8 9 |
SELECT TableA.*, TableB.*, TableC.*, TableD.* FROM TableA JOIN TableB ON TableB.aID = TableA.aID JOIN TableC ON TableC.cID = TableB.cID JOIN TableD ON TableD.dID = TableA.dID WHERE DATE(TableC.date)=date(now()) |
SQL Join Multiple Tables With Conditions WHERE Clause
let’s us see the following example of SQL SELF JOIN with WHERE Clause, we will use the table TEACHER twice and in order to do this we will use the alias of the table. Therefore, to get the list of Teacher and their Teacher name the following SQL statement has used:
SQL Statement:
|
1 2 3 4 |
SELECT a.TeacherID AS "TeacherID",a.Teacher_Name AS "Teacher Name", b.TeacherID AS "Teacher_Dept",b.Teacher_Name AS "Supervisor Name" FROM Teacher a, Teacher b WHERE a.Teacher_Dept = b.TeacherID; |
SQL Join Multiple Tables With Conditions With WHERE clause: FULL OUTER JOIN
Therefore, we can add a WHERE clause with a FULL OUTER JOIN [+] to get rows with no matching data between both Join tables.
In the given below the following query, we add a WHERE clause to show only records having NULL values.
|
1 2 3 4 5 |
SELECT * FROM Students FULL OUTER JOIN College ON Students.StudentID = College.StudentID WHERE Students.StudentID IS NULL OR College.StudentID IS NULL; |
Therefore, Execute this command and view the output. you can see that it only returns rows that do not match either in Student or College table.
Conclusion:
In this article, you have learned SQL Join Multiple Tables With Conditions means that SQL multiple joins concept such as Inner Join, Left Join, Right Join, Outer Join with a practical example. I hope you will enjoy it!
About Author
