SQL Join ExampleSQL JOINThe user can implement an SQL Join to merge data from two or more tables. In a relational database management system, all the data is related to one another. The data may be stored in different tables but somehow related. The different fields in the tables are composed of entities with relations. The data can even be separated into several tables within a database. If there is a scenario where the user requires to collect data from several tables simultaneously, then the user can implement a simple Join clause to perform data retrieval. The join clause will merge the fields from different tables that will be associated based on the common data values in each table. A join clause can be implemented with respect to join prediction. The predicate is mentioned in a WHERE clause. The predicate can be regarded as a condition the tables must meet to merge the data values. For Example, when performing the equality test between the values in similar columns of different tables. These values may be present in fields with different names. It will also be regarded as a join predicate. The user can implement different operators in a join-predicate. It includes BETWEEN, LIKE, NOT, and comparison operators such as <, >, <>, <=, >=, !=. SQL provides support for different types of join. The types of Join in SQL can be divided based on how data across the tables are merged. The types of joins are as follows: Inner Join: An Inner Join returns the intersection of data values in the field of two or more tables. It begins with comparing the data values in the fields of the first table to the data values present in the second table. It then pairs the row and checks if the two rows meet the criteria specified in the join-predicate. It merges the records. It is also known as default join. Outer Join: It is the opposite of the inner join. It returns all the records even if it has records in the table with no counterpart present in the other table. The outer join can further be classified into three subcategories. These categories are as follows
Syntax of Join In SQLThe syntax to implement the join clause in two tables is as follows: For Example, Consider a scenario where the user has two tables in a local database. The database contains records of students and courses provided in the table. Different students are assigned different courses. The first table is the Students table, which contains the personal details of all the students in the college. It has fields for S_ID, S_Name, S_Age, S_City.
Implement the below SQL query to create the above table in the database. To ensure that the above table is successfully inserted into the database, view the table by implementing the below query. The second table contains related to the course. The fields in the Course table are C_ID, C_Name, SE_ID, and CT_Name.
Implement the below SQL commands to create the Courses table. Implement the below SQL query to view the Courses table. To merge the Student and Course table data, we will implement the essential Join operation on the two tables using the Join keyword and SELECT clause. The query to join these tables is as follows: You will get the below result after implementing the above SQL query. Output: The above results are similar to those the user can retrieve by retrieving all the fields from the Students and Courses table. The JOIN keyword has merged the records from both tables into a singular tabular representation. The user can use another method to implement the same operation by implementing the SELECT query to retrieve the same information. Output: Types Of Joins and its ExampleThe user can implement two significant types of join. The two types are:
Example of Inner JoinInner Join A common join type enables users to merge two tables based on a standard column. It means that all the columns in the first table are checked with all the columns in the second. It merges all the columns that meet the conditions. Let's consider a Customers table that contains the CustomerID, CustomerName, and City.
Now create the second table, the Orders table, that will store the OrderID, CustomerID, Product, and the ordered product quantity. The table is as follows:
Implement the below query to create the above tables and implement the Inner Join. Output: Example of Outer JoinOuter Join Outer Joins enable the user to have all the records from either one or both tables in the result. There are three types of Outer Join in SQL. The three types include Left, Right, and Full Outer Join. We will use the above table to implement the Outer Join. The query to implement the outer join is as follows: It will give the following result: Example of Self JoinSelf Join As the name suggests, it enables the user to merge the table. Consider a situation where you have the employee record and their manager id. Since the manager is an employee, their records are also in the same table.
Output: Next TopicSQL Not Equal |