<div class="image-550">
<img src="https://cdn.kahimyang.com/resources-22/sql-joins.avif" alt="Using JOINs in SQL Queries: Use Cases and Examples" />
</div>
<h4>
INNER JOIN
</h4>
<p>
INNER JOIN is used when you want to retrieve records that have matching values in both tables. For instance, if you have a "products" table and a "categories" table, you can use an INNER JOIN to find the actual product for each product category using a category_id field from both tables.
</p>
<pre>
<code class="language-sql line-numbers normalize-whitespace">
SELECT products.product_id, products.product_name, 
       categories.category_name
FROM products
INNER JOIN categories
ON products.category_id = categories.category_id;
</code>
</pre>
<p>
In the example above, the INNER JOIN clause combines each row from the "products" table with the "categories" table where the value of "category_id" fields in both tables are equal. 
</p>
<p>
You can also join more than two tables. For example, if you have a third table "orders", you can join it with "products" and "categories" like this:
</p>
<pre>
<code class="language-sql line-numbers normalize-whitespace">
SELECT orders.order_id, products.product_name, 
      categories.category_name
FROM ((orders
INNER JOIN products ON orders.product_id = 
      products.product_id)
INNER JOIN categories ON products.category_id = 
      categories.category_id);
</code>
</pre>
<p>
In this query, each row in "orders" table is combined on the related rows in tables "products" and "categories".  Remember, the INNER JOIN selects records that have matching values in both tables. If there is no match, the result is NULL.  In this example, products in the order that has no matching category_id in catergoties table will NULL or the record will not be returned to the result set.
</p>
<h4>
LEFT JOIN or LEFT OUTER JOIN
</h4>
<p>
Use LEFT JOIN if you want to retrieve all records from the left table and the matched records from the right table. If there is no match, the result is NULL on the right side. For example, you can use a LEFT JOIN to show all "customers" alongside the "customers", if they exist.
</p>
<pre>
<code class="language-sql line-numbers normalize-whitespace">
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_name;
</code>
</pre>
<p>
In this example, the LEFT JOIN clause combines each row from the "customers" table with the "orders" table where the "customer_id" fields in both tables are equal. If the customer_id in the "customers" table has no match in the "orders" table, customers.customer_name (from the left table in this example, the customers table) will be returned but value of orders.order_id (from the right table, the orders table) will be NULL.
</p>
<div class="article-comment">
<p>
Remember, the "left" and "right" table designations simply refer to the order in which the tables are listed in the JOIN clause. The "left" table is the one that appears first, and the "right" table appears second.
</p>
</div>
<p>
LEFT JOIN on more than two tables. For example, if you have a third table "regions", you can join it with "countries" and "locations" like this:
</p>
<pre>
<code class="language-sql line-numbers normalize-whitespace">
SELECT region.region_name, country.country_name, 
     location.street_address, location.city
FROM regions 
LEFT JOIN countries
ON country.region_id = region.region_id
LEFT JOIN locations
ON locations.country_id = country.country_id;
</code>
</pre>
<p>
In this query, each row in "regions" is combined with the related rows in "countries" and "locations".
</p>




The Basics of SQL JOINs: Use Cases and MySQL/MariaDB Examples