Quick Enquiry Form

×

    JOINS in SQL

    joins-in-sql
    Blog

    JOINS in SQL

    Introduction of JOINS in SQL

    Using a relational database, you can store data in several logical tables that are all linked together. To get a comprehensive and useful collection of data, you must combine these tables and query data from each one. Joins in SQL Server can take a variety of forms. Data from one table is used to identify rows in another table using the join type. This article will teach you about the various “SQL Server joins” available for combining data from many tables.

    Describe JOINS in SQL

    It is possible to extract data from many tables by using SQL JOINS. A SQL JOIN operation is carried out anytime a SQL query contains references to two or more tables.

    There are four distinct kinds of joins that can be performed with SQL:

    • INNER JOIN in SQL (Referred to as a SIMPLE JOIN)
    • LEFT OUTER JOIN in SQL (Referred to as a LEFT JOIN)
    • RIGHT OUTER JOIN in SQL. (Referred to as a RIGHT JOIN)
    • FULL L OUTER JOIN (Referred to as a FULL JOIN)

    Thus, let’s talk about the syntax of SQL JOINS, have a look at several graphical representations of SQL JOINS, and investigate some cases.

    INNER JOIN In SQL

    As long as the requirement is met, the INNER JOIN keyword will select every row from each of the tables. This keyword will combine all rows from both tables where the criteria are met, i.e. the related field’s value will be the same, to produce the resultant set.

    Syntax

    The syntax for the SQL INNER JOIN is written as follows:

    SELECT columns

    FROM table A

    INNER JOIN table B

    ON table A.column = table B.column;

    Table A = first table A

    Table B = Second table B

    Visual Representation

    The entries where table A and table B intersect will be returned using an INNER JOIN in SQL

    Example

    Let’s examine a query that demonstrates the use of the INNER JOIN.

    In this illustration, the table called students has the following information: Table A
    Register numberStudent NameStateAge
    01AmuthaAndhra17
    02BalaKerala17
    03ChandruKarnataka16
    04DanielMaharastra18
    05ElizaOrissa19
    06FrankPunjab18
    07GaneshTamil Nadu17
    08HanaDelhi19

    And a table B regarding subjects with the following information: Table B

    Language Register number
    Hindi01
    Tamil02
    Malayalam03
    Hindi05
    English07
    Tamil08
    Marathi10
    Hindi11

    SELECT students.register_number, subjects.language, students.age

    FROM students

    INNER JOIN language

    ON students.register_number = subjects.register_number

    SUBJECT BY students.registration_number;

    Output
    Register numberSubjectAge
    01Hindi17
    02Tamil17
    03Malayalam16
    05Hindi19
    07English17
    08Tamil19

    If there is a matching register number entry in both the students and subjects tables, this example would retrieve all rows from both tables.

    Since they don’t exist in either table, the entries in the student’s table with register numbers entries 04 and 06 would be excluded. Since the register number 10 and 11 does not exist in the student’s table, the record from the subjects table would be removed.

    LEFT OUTER JOIN IN SQL

    This join retrieves all of the rows from the table on the join’s left side and matches rows from the table on the join’s right side. The result-set will include null for any rows that don’t have a matching row on the right. Another name for LEFT JOIN is LEFT OUTER JOIN.

    Syntax:

    SELECT table A.column1,table1.column2,table2.column1,….

    FROM table A

    LEFT JOIN table B

    ON table A.matching_column = table B.matching_column;

    Table A: First table A.

    Table B: Second table B

    matching_column: Column that is common to both the table A and table B.

    Visual Representation

    Example For (LEFT OUTER JOIN):

    SELECT Student.NAME, Subject.Language

    FROM Student

    LEFT JOIN Subject

    ON Subject.Register_number = Student.Register_number;

    Output
    Student NameLanguage
    AmuthaHindi
    BalaTamil
    ChandruMalayalam
    DanielNull
    ElizaHindi
    FrankNull
    GaneshEnglish
    HanaTamil

    RIGHT OUTER JOIN IN SQL

    Complementary to LEFT JOIN is RIGHT JOIN. This join gives back all the rows from the table on the right side of the join as well as any matching rows from the table on the left. The result-set will include null for any rows for which there is no corresponding row on the left.

    Syntax

    SELECT table A.column1,table A.column2,tableB.column1,….

    FROM table A

    RIGHT JOIN table B

    ON table A.matching_column = table B.matching_column;

    Table A: First table A.

    Table B: Second table B

    matching_column: Column that is in common to both the table A and Table B.

    Visual Illustration

    Example of (RIGHT JOIN)

    SELECT Student.NAME, Subject. Language

    FROM Student

    RIGHT JOIN Subject

    ON Subject.Register_number = Student.Register_number;

    Output
    Student NameLanguage
    AmuthaHindi
    BalaTamil
    ChandruMalayalam
    ElizaHindi
    GaneshEnglish
    HanaTamil
    NULLMarathi
    NULLHindi

    FULL JOIN IN SQL

    By integrating the results of both LEFT JOIN and RIGHT JOIN, FULL JOIN produces the result-set. The rows from both table A and table B are all included in the result set. The result-set will include NULL values for the rows where there was no match.

    Syntax

    SELECT table A.column1,table A.column2,table B.column1,….

    FROM table A

    FULL JOIN table B

    ON table A.matching_column = table B.matching_column;

    Table A: First table A.

    Table B: Second table B

    matching_column: Column that is in common to both the table A and table B.

    Visual Representation

    Example For FULL JOIN

    SELECT Student.NAME, Subject.Language

    FROM Student

    FULL JOIN Subject

    ON Subject.Register_number = Student.Register_number;

    Output
    Student NameLanguage
    AmuthaHindi
    BalaTamil
    ChandruMalayalam
    DanielNULL
    ElizaHindi
    FrankNULL
    GaneshEnglish
    HanaTamil
    NULLMarathi
    NULLHindi

    Conclusion

    Thus the JOINS in SQL and their different types are discussed extensively in this blog with precise examples to assist you in understanding the concept in depth. Hope you enjoyed learning about JOINS in SQL.

    python-training-in-chennai

    100% Placement Support

    Live Online & Classroom
    Python Programming

    Discover your best career by learning on-demand Python Programming

    data-science-training-in-chennai

    Real Time Hands-on Training

    Live Online & Classroom
    Data Science Training

    Our training course will give you the required skills..

    machine learning in chennai

    Learn From Industry Experts

    Live Online & Classroom
    Machine Learning Training

    Machine learning is one of the buzzwords recently and this can be attributed…

    rpa-training-in-chennai

    No Cost EMI Option

    Live Online & Classroom
    RPA Training

    Discover your best career by learning on-demand RPA technology…

    software-testing-in-chennai

    Value-Based Certification

    Live Online & Classroom
    Software Testing Course

    Want to get career-oriented Software Testing Training in Chennai Then think about SLA Institute…

    java-training-in-chennai

    Lifetime Career Guidance

    Live Online & Classroom
    Java Training

    Our training course will give you the required skills to be one of the best picks by the IT employers…

    selenium-training-in-chennai

    Flexible Learning Hours

    Live Online & Classroom
    Selenium Testing

    Our training course will give you the required skills to be one of the best picks by the IT employers…

    dotnet-training-in-chennai

    Convenient Training Modes

    Live Online & Classroom
    Dot Net Training

    Discover the great opportunities in Dot Net by practicing on real-life implementation strategies for delivering…

    ccna-training-in-chennai

    Convenient Training Modes

    Live Online & Classroom
    CCNA Training

    The CCNA certification helps you in becoming a sound, well-equipped network engineer…

    php-course-in-chennai

    Advanced Course Curriculum

    Live Online & Classroom
    PHP Training

    Being a language for general purposes, PHP can develop both static and dynamic websites…

    full-stack-developer-training

    Experiential Full Stack Course

    Live Online & Classroom
    Full Stack Development

    Learn Full Stack Developer course from SLA Institute…

    1
    Softlogic-Academy