{"id":2955,"date":"2021-09-25T09:50:38","date_gmt":"2021-09-25T04:20:38","guid":{"rendered":"https:\/\/binaryterms.com\/?p=2955"},"modified":"2021-09-25T14:04:22","modified_gmt":"2021-09-25T08:34:22","slug":"join-operations-in-sql","status":"publish","type":"post","link":"https:\/\/binaryterms.com\/join-operations-in-sql.html","title":{"rendered":"Join Operations in SQL"},"content":{"rendered":"

Join operations in SQL integrates two relations from the database to form a new relation. The relations are integrated or merged based on the common columns present in the two relations. In the section ahead, we will be discussing all forms of join in SQL.<\/p>\n

Types of Join Operations in SQL<\/h2>\n
    \n
  1. Natural Join<\/a><\/li>\n
  2. Equi Join<\/a><\/li>\n
  3. Theta Join<\/a><\/li>\n
  4. Semi Join<\/a><\/li>\n
  5. Outer Join<\/a><\/li>\n<\/ol>\n

    <\/a><\/p>\n

    Natural Join<\/h3>\n

    The natural join operation integrates two relations on the basis of common columns present in the relation. Though the relations have common attributes the result of natural join has only one occurrence of the common attribute as the duplicate attribute is eliminated at the time of computation.<\/p>\n

    Most of the systems require that the common attributes present in the relations on which natural join has to be performed must have the same name as it becomes easy to identify the attributes that are used to perform join.<\/p>\n

    In this case, if the common attributes have different names you have to use rename clause. Though in some systems natural join allows the names of common attributes to be used for joining to have different names they must have the same domain.<\/p>\n

    Now let us understand the natural join with the help of an example. We have two relations professor(prof_id, first_name, last_name, section) and student(stud_id, prof_id, first_name, last_name). Now, here the common attributes are a professor.prof_id and student.prof_id.<\/p>\n

    \"Professor<\/p>\n

    The query applying natural join on the relations professor and student is as follow:<\/p>\n

    SELECT *
    \nFROM professor
    \nJOIN student
    \nON professor.prof_id= student.prof_id;<\/p>\n

    This join query would result in the following relation.<\/p>\n

    \"Natural<\/p>\n

    Now you can see that the table above has so many columns so we can modify the query to display some specific attributes only as:<\/p>\n

    SELECT p.first_name, p.last_name,
    \ns.first_name, s.last_name
    \nFROM professor AS p
    \nJOIN student AS s
    \nON p.prof_id= s.prof_id;<\/p>\n

    The result of the modified query is given below.<\/p>\n

    \"Natural<\/p>\n

    But there is again a problem we have two first_names and two last_names which is creating confusion. So, let us again modify this query.<\/p>\n

    SELECT
    \np.first_name AS prof_fn, p.last_name AS prof_ln,
    \ns.first_name AS stud_fn, s.last_name AS stud_ln
    \nFROM professor AS p
    \nJOIN student AS s
    \nON p.prof_id= s.prof_id;<\/p>\n

    Now you can easily identify the professor\u2019s first and last name and the student\u2019s first and last name.
    \n\"Natural<\/p>\n

    <\/a><\/p>\n

    Equi Join<\/h3>\n

    The equijoin is a basic join where the join condition is equality. The comparison operator used in the join condition is \u2018=\u2019.<\/p>\n

    SELECT *
    \nFROM Table 1
    \nJOIN Table 2
    \nON (join_conditon is equality);<\/p>\n

    Consider that we have two relations employee(emp_id, designation, dept) and student(dept_name, dept).<\/p>\n

    \"Employee<\/p>\n

    Here the common attribute on which these relations can be joined is dept. so let us apply equijoin on these two relations<\/p>\n

    SELECT *
    \nFROM employee AS E
    \nJOIN department AS D
    \nON (E.dept = D.dept);<\/p>\n

    The result relation for this query is as below:
    \n\"Equi<\/p>\n

    <\/a><\/p>\n

    Theta Join<\/h3>\n

    Theta join is a join where the join condition is other than equality. If the join condition is equality then it comes out to be an equijoin. The join condition in theta joins include the comparison operators such as >, <, >=, <=.<\/p>\n

    To illustrate theta join, just let us take an example. Consider we have two relations customer and product.<\/p>\n

    \"Customer<\/p>\n

    Now if we have to display the tuples from both the tables where the credit limit is greater than the products price then the query will be:<\/p>\n

    SELECT *
    \nFROM customer
    \nJOIN product
    \nON (customer.credit_limit > product.price);<\/p>\n

    \"Theta<\/p>\n

    \n