Using SQL JOIN (USJ)

According to Ramakrishnan and Gehrke (2005, p107), there are several forms of SQL joins, namely:

1. Condition Joins

The most common use of joins is the conditional join form, which performs a selection condition on the cross-product between two relations (R and S).

σc (RXS)

c is a condition based on the results of the R and S relations in searching for data in the related columns.

2. Equijoin

The equijoin operation is the same as when performing a query with a relational merge by searching for the same data value in the related columns between the two relations. Illustrated with the query: R.name1 = S.name2, where R and S are each tables to search for the same data value in the name1 column in the R table and the name2 column in the S table.

3. Natural Join

The natural join operation is an equijoin operation that has equality on all fields that have the same name in table R and table S.

In this case, it allows us to omit the condition in the join operation, as it would result in two fields with the same name.

In Oracle, SQL commands will be analyzed by the Optimizer, which will determine what steps are most optimal in executing SQL commands. The following is how to JOIN in the ORACLE database:

4. Nested Loop

Nested Loop is an effective JOIN if the subsets being joined are small in number and if the conditions in the JOIN command are efficient for joining the 2 tables. How Nested Loop works is:

  1. The optimizer determines a table to be used as an Outer Table.
  2. The remaining table is made into an Inner Table.
  3. For each row in the Outer Table, the Optimizer will access all rows in the Inner Table with the conditions specified in the JOIN.

Nested Loop will be selected by Optimizer if it meets 2 conditions, namely:

  • The number of rows in the table is small.
  • There are optimal conditions for accessing rows in the Inner Table.

5. Hash Joins

Hash Joins are usually used to combine large amounts of data.

How Hash Joins work is that the Optimizer creates a Hash Table based on the JOIN predicate. Each table in INNER and OUTER is each made into a code with a Hash Function, then each Hash code from INNER will be compared with the Hash Code from OUTER, if the Hash code from INNER and OUTER is the same, then the process of checking the value of the column will be carried out, which will eventually be included in the results, if the column value is the same.

6. Sort Merge Joins

Sort Merge Joins is usually used to combine rows from two unrelated sources. Usually Hash Joins have better performance than sort Merge Joins. However, Sort Merge Joins will work better than Hash Joins if the following conditions exist:

  • The rows are sorted
  • Sorting operation is not necessary to be performed.

Sort Merge Join is very useful if the JOIN condition between two tables is not in the form of the same condition but has a condition form such as <, <=, >, or >=.

7. Cartesian Joins

A Cartesian Join is used when one or more tables have no join conditions with respect to the other tables. The optimizer will combine each row in the first table with each row in the other table to produce a Cartesian Product of the two sets.

8. Outer Joins

Outer Joins have a process where in addition to rows that meet the JOIN conditions are included in the results. Outer Joins will add rows that do not meet the JOIN conditions but are combined with NULL values. There are 3 types of Outer Joins, namely:

Right Join

The Right Join command will return rows that satisfy the JOIN condition and also rows in the right table that do not satisfy the JOIN condition by adding NULL.

Left Join

The Left Join command will return rows that satisfy the JOIN condition and also rows in the left table that do not satisfy the JOIN condition by adding NULL.

Full Outer join

Full Outer Join is a JOIN that functions as a combination of Left Join and Right Join where in addition to the results of the Inner Join being included in the results table, the rows of the table that are not included in the results will be added with NULL and included in the results table.

Nested Loop Outer Join

This operation will be used when an Outer Join is used between 2 tables where the Outer Join will return rows in the Outer Table even though it does not meet the JOIN condition. This raises 2 conditions, namely:

  • If the rows in the Inner Table meet the conditions then add the columns in the Inner Table to the Outer Table and display them in the results.
  • If no rows in the Inner Table meet the conditions in the Outer Table, then display the rows in the Outer Table with all columns in the Inner Table replaced with NULL.

9. Sort Merge Outer Join

The Optimizer will use Sort Merge for Outer Join if:

  • If the use of Nested Loop Join is not optimal due to the large amount of data and the absence of sufficiently efficient JOIN conditions.
  • If the Optimizer finds that using Sort Merge will improve performance over Hash Join because the sorting operation in Sort Merge does not need to be performed again.

You can read more about the implementation of the above concepts here:

JOIN Query Showing Sold Items DB Northwind

Excuse me Admin, sorry I'm still a newbie in learning SQL. In class, I have an assignment which asks me to display the number of items sold from a database, coincidentally the database I use to learn is the northwind database, how do I do that, bro? please enlighten me, thank you very much for your help.

Below I attach the contents of the database tables:

Answer

It's easy, bro, you can use this method:

SELECT
c.CategoryName, count(p.categoryID) Jumlah
FROM
products AS p JOIN categories AS c
ON p.CategoryID=c.CategoryID
JOIN order_details AS od
ON od.ProductID=p.productID
GROUP BY 1 ORDER BY 2 DESC LIMIT 5

Here are the results;

Showing Sold Item Categories Report

I use the northwind database to learn SQL programming, the case study is how I can display the report with output more or less like the following:

| bulan  | total | beverage |       |
----------------------------------------
|januari |  7
|        |
|febuari |  8 
|        | 
|dst..   |  9

Please enlighten me, bro. Thank you. Greetings newbie!

Answer

You can use the following command:

SELECT
MONTHNAME(o.OrderDate) AS Bulan,
SUM(od.Quantity) AS Total,
CategoryName AS category
FROM
order_details AS od JOIN orders AS o JOIN categories AS c
ON
o.OrderID=od.OrderID
/*products AS p JOIN categories AS c
ON p.CategoryID=c.CategoryID*/
WHERE YEAR(o.OrderDate)=1997 GROUP BY 1 ORDER BY
MONTH(o.OrderDate) ASC

This command produces output that is more or less the same as what you intended, here's what it looks like:


Post a Comment

Previous Next

نموذج الاتصال