SQL

Understand SET Operators

Hi All,

I assume you heard about SET, usually it can be heard in Mathematics and SET operators in SQL has the same meaning as that one as well for UNION and INTERSECT.

SET operators includes

  • UNION
  • INTERSECT
  • EXCEPT
  • APPLY

Union

It returns a result set of distinct rows combined from both sides. This means that it will display all the records from both table but will not show the duplicate rows, it’s like as if every record is unique.

My own concept: ((Purchasing + Sales) – Duplicates).

Capture

Union ALL

It returns a result set with all rows from both sets. Consider that it display all the records from each of the table. Duplicates will occurs here.

My own concept: (Purchasing + Sales).

Intersect

It returns only distinct rows that appear in both result sets. The results shows only exist in both table.

My own concept: (Purchasing & Sales)

Capture

Except

It returns only distinct rows that appear in the left set but not the right.

My own concept: (Sales – (Purchasing & Sales) – Purchasing)

Capture

Apply

**I have no idea how this works, do you? mind explain it to me ^.^


-Farrah K.

SQL

Understand JOIN Statements

Hi all,

Before this, I had a bit confuse on some of the SQL statement especially the JOIN statements. Ok, Let’s see…

JOIN statements

There are 3 types of JOIN:

  • Cross
  • Inner
  • Outer

Cross

It basically combines all the rows in the both tables. Meaning that if you have 20 rows in table 1 and 8 rows in table 2, therefore, the number of rows will be after cross join both of them is 20*8 = 160 rows (Cartesian Product).

Inner

Returns only rows where a match is found in both tables.  So when there is comparison between 2 tables it will only search and display the matching rows.

Outer

Returns all rows from one table and any matching rows from second table. This mean that it will display all the rows in table 1 and display only the matching rows from the table 2. (this example is the left join).

In outer join there are 3 type of outer join which they are:

  • Left

Note: It will return all the rows in the left table even if there are no matches in the right table.

  • Right

Note: It will return all the rows in the right table even if there are no matches in the left table.

  • Full

Note: It will return all the rows in the left table 1 and in the right table, If there are no matches in either of the table, those rows will be listed as well.


That’s it. Hope this helps.

– Farrah K.