{"id":1887,"date":"2019-01-15T12:00:00","date_gmt":"2019-01-15T11:00:00","guid":{"rendered":"https:\/\/kindsonthegenius.com\/blog\/all-about-sql-joins-simple-explanation\/"},"modified":"2026-07-05T03:22:11","modified_gmt":"2026-07-05T01:22:11","slug":"all-about-sql-joins-simple-explanation","status":"publish","type":"post","link":"https:\/\/kindsonthegenius.com\/blog\/all-about-sql-joins-simple-explanation\/","title":{"rendered":"All About SQL Joins (Simple Explanation)"},"content":{"rendered":"

I would would cover everything you need to know about SQL Joins in this lesson. And you will see it’s quite clear.<\/p>\n

A JOIN in SQL is used to combine record from two or more tables. The combinations is based on a given criteria. Also note that the two tables need be related in some way.<\/p>\n

We would be working with the following tables.<\/p>\n

 <\/p>\n

Customers Table: Contains list of customers<\/strong><\/p>\n

[table id=3 \/]<\/p>\n

 <\/p>\n

Orders Table: Contains orders placed by the customers<\/strong><\/p>\n

[table id=4 \/]<\/p>\n

 <\/p>\n

We would cover :<\/p>\n

    \n
  1. Inner Join<\/a><\/li>\n
  2. Left Join<\/a><\/li>\n
  3. Right Join<\/a><\/li>\n
  4. Full Join<\/a><\/li>\n
  5. Cross-Join<\/a><\/li>\n
  6. Self Join<\/a><\/li>\n<\/ol>\n

     <\/p>\n

    1. INNER JOIN<\/strong><\/h4>\n

    You use INNER JOIN to return records that have matching values in both tables.\u00a0 Note that this is the same as JOIN. Therefore, if you use only JOIN, the it assumes INNER JOIN.<\/p>\n

    Now copy and execute the code below in SQL Server.\u00a0 You will have the result in Table 1.0.<\/p>\n

    <\/p>\n

    \n
    SELECT<\/span> c<\/span>.Contact, c<\/span>.CustomerID, o.CustomerID, o.OrderDetails  \nFROM<\/span> Customers c<\/span> INNER<\/span> JOIN<\/span> Orders o\nON<\/span> c<\/span>.CustomerID =<\/span> o.CustomerID\n<\/pre>\n<\/div>\n
    Listing 1.0: INNER JOIN (JOIN)<\/p>\n
    [table id=5 \/]
    \nTable 1.0: INNER JOIN (JOIN)<\/p>\n
    You should note in Table 1.0, that only the records where there is matching CustomerID is returned. I would recommend you try this yourself. You can get the excel table from here. Then see how to import excel to MSSQL from here.<\/p>\n
     <\/p>\n
    2. LEFT JOIN<\/strong><\/h4>\n
    Use LEFT JOIN to return all records in the left table plus matching records in the right table. Null is used in place of missing values in the right table. Note that LEFT JOIN is the same as LEFT OUTER JOIN.<\/p>\n
    I have written the query. You can copy and paste the code below in SQL Server. Then run it. You will have the result shown in Table 1.1<\/p>\n
    <\/p>\n
    \n
    SELECT<\/span> c<\/span>.Contact, c<\/span>.CustomerID, o.CustomerID, o.OrderDetails  \nFROM<\/span> Customers c<\/span> LEFT<\/span> JOIN<\/span> Orders o\nON<\/span> c<\/span>.CustomerID =<\/span> o.CustomerID\n<\/pre>\n<\/div>\n
    Listin 1.1: SQL Query for LEFT JOIN (LEFT OUTER JOIN)<\/p>\n
    [table id=6 \/]<\/p>\n
    Table 1.1: Table for LEFT JOIN(LEFT OUTER)<\/p>\n
    We see that in Table 1.1, all the records for Customers Table (Left Table) is included. Furthermore, for customers with no orders, NULL is used.<\/p>\n
     <\/p>\n
    3. RIGHT JOIN<\/strong><\/h4>\n
    You use RIGHT JOIN to return all records from the right table table plus matching records in the left table. It is same as RIGHT OUTER JOIN.\u00a0 Null is used in place of missing values in the right table. I have written the query for you. If we run the code below, then we would have the result in Table 1.3. You can view RIGHT JOIN as opposite of LEFT JOIN. I recommend you try it yourself.<\/p>\n
    <\/p>\n
    \n
    SELECT<\/span> c<\/span>.Contact, c<\/span>.CustomerID, o.CustomerID, o.OrderDetails  \nFROM<\/span> Customers c<\/span> RIGHT<\/span> JOIN<\/span> Orders o\nON<\/span> c<\/span>.CustomerID =<\/span> o.CustomerID\n<\/pre>\n<\/div>\n
    Listing 1.3: SQL Query for RIGHT JOIN (RIGHT OUTER JOIN)<\/p>\n
    You can see in Table 1.3 that all the records for the Orders table (right table) is included.<\/p>\n
     <\/p>\n
    [table id=7 \/]<\/p>\n
    Table 1.3: Result of RIGHT JOIN (RIGHT OUTER JOIN)<\/p>\n
     <\/p>\n
    4. FULL JOIN<\/strong><\/h4>\n
    We use FULL JOIN to return all records from both tables. You can view this as a combination of LEFT JOIN\u00a0 and RIGHT JOIN.\u00a0 I have written the query as shown below. I also recommend you try it yourself.<\/p>\n
    <\/p>\n
    \n
    SELECT<\/span> c<\/span>.Contact, c<\/span>.CustomerID, o.CustomerID, o.OrderDetails  \nFROM<\/span> Customers c<\/span> FULL<\/span> JOIN<\/span> Orders o\nON<\/span> c<\/span>.CustomerID =<\/span> o.CustomerID\n<\/pre>\n<\/div>\n
    Listing 1.4: SQL Query FULL JOIN (FULL OUTER JOIN)<\/p>\n
    [table id=8 \/]<\/p>\n
    Table 1.4: FULL JOIN<\/p>\n
     <\/p>\n
    5. CROSS JOIN<\/strong><\/h4>\n
    You use CROSS JOIN to return a result that is number of rows of first table multiplied by number of rows in second table.\u00a0 We also call this Cartesian Product. For our example, the output would be a 25 rows table!<\/p>\n
    I would only provide the query for you. So you run it yourself and see the result. You have the SQL query in Listing 1.5. So run it and leave a comment to let me know what you have.<\/p>\n
    <\/p>\n
    \n
    SELECT<\/span> c<\/span>.Contact, c<\/span>.CustomerID, o.CustomerID, o.OrderDetails  \nFROM<\/span> Customers c<\/span> CROSS<\/span> JOIN<\/span> Orders o\n<\/pre>\n<\/div>\n
    Listing 1.5: CROSS JOIN (Cartesian Product)<\/p>\n
    Not that the SQL query for CROSS JOIN does not include the ON keyword<\/p>\n
     <\/p>\n
    6. Self Join<\/strong><\/h4>\n
    We use to indicate a join of a table with itself. I would provide you with an example of this as shown in Listing 1.6.<\/p>\n
    <\/p>\n
    \n
    SELECT<\/span> c1.CustomerName, c2.CustomerID \nFROM<\/span> Customers c1 JOIN<\/span> Customers c2\nON<\/span> c1.CustomerID =<\/span> c2.CustomerID\n<\/pre>\n<\/div>\n
    Listing 1.6: Self Join<\/p>\n
    The result of this code is given in Table 1.5<\/p>\n
    [table id=9 \/]<\/p>\n
    Table 1.5: Self Join<\/p>\n
    Also note that we don’t write a ‘SELF JOIN’ query.\u00a0 We simple join a table with itself using any of the joins we have discussed.<\/p>\n","protected":false},"excerpt":{"rendered":"
    I would would cover everything you need to know about SQL Joins in this lesson. And you will see it’s quite clear. A JOIN in … <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[311],"tags":[],"class_list":["post-1887","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1887","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/comments?post=1887"}],"version-history":[{"count":1,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1887\/revisions"}],"predecessor-version":[{"id":2055,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1887\/revisions\/2055"}],"wp:attachment":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/media?parent=1887"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/categories?post=1887"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/tags?post=1887"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}