How Select Statement SQL Works

Berikut adalah cara kerja SELECT statement pada Query SQL (setidaknya secara teori):

1. Dimulai dari FROM clause dan membuat sebuah table dari semua join, union, intersection dan apapun itu pada FROM clause. Pada momen ini diijinkan untuk membuat alias pada table ( AS ) yang akan digunakan untuk query statement berikutnya.
   
   
2. Menuju ke WHERE clause dan membuang semua row/record yang tidak masuk kriteria; selalu menolak suatu kondisi yang unknown dan false. WHERE clause berkaitan dengan proses kerja dari FROM clause.
   
   
3. Menuju ke GROUP BY clause (jika ada), melakukan grouping dan mengurangi tiap group yang ada menjadi single row, me-replace working table yang ada dengan grup table yang didefinisikan. Row/record yang akan di grouping harus memenuhi karakteristik: (1) kolom table (2) Perhitungan terhadap grouping tsb (cth, aggregate functions) (3) function atau (4) suatu ekspresi yang dibuat dari no 1, 2 dan 3.
   
   
4. Menuju ke HAVING clause (jika ada) dan menerapkannya terhadap grouping table yang sudah terbentuk. Jika tidak ada, maka dianggap keseluruhan table sebagai satu grup.
   
   
5. Menuju ke SELECT clause dan mengkonstruksi/menggabungkan ekspresi yang terdapat di SELECT clause. Ini berarti semua scalar subqueries, function calls dan expression yang terdapat pada SELECT dijalankan ketika semua clause selesai dieksekusi. "AS" operator pada SELECT statement yang berfungsi sebagai pemberi nama/alias juga dieksekusi sebagai satu kesatuan, karena itu ia tidak dapat digunakan pada WHERE clause ataupun SELECT list yang lain.
   
   
6. Untuk nested query atau query rangkap mengikuti aturan umum yang terdapat pada bahasa pemograman seperti C, VB, Java, dll. Yaitu cakupan/scopes pada referensi kolom atau table yang digunakan. Contohnya: table atau kolom pada nested query hanya dikenal dari nested query yang dicakupnya, namun ia bisa menggunakan reference table atau kolom diluarnya dimana nested query itu digunakan.

Ex: Select Id, name, category, (Select name from Supplier As sup Where prodid.sup = id.Prod) as SupplierName
From Products prod

Note: SELECT tidak dapat memiliki lebih banyak kolom daripada GROUP BY, tetapi dapat memiliki lebih sedikit kolom dibanding GROUP BY.

Jika ada salah, mohon koreksinya :) Maksud hati hanya mao sharing yang saya tau kok :D

Using Alias Select Statement as Where Clause

Sengaja menggunakan judul bahasa Inggris, karena menurut saya lebih mudah dicerna.
Intinya adalah "Bagaimana menggunakan Alias yang terdapat pada Select statement untuk Where clause"

Misal:
Select concat(FirstName, LastName) as FullName
from Employees
where FullName = 'Im Yoona'

Query diatas akan menimbulkan error, karena WHERE adalah T-SQL, WHERE clause selalu dieksekusi terlebih dahulu sebelum SELECT statement, sehingga alias FullName tidak akan terdefinisikan oleh WHERE clause.
Jika anda belum tahu/lupa dengan cara kerja garis besar query pada sql (untuk SELECT statement), bisa dilihat pada artikel :
How Select Statement Works

Ingat: pada SQL Server fungsi concat belum tentu ada, biasanya diganti dengan '+' (tanpa petik).
Hal tersebut mudah saja untuk diselesaikan, yaitu dengan menaruh formula concat(FirstName, LastName) = 'Im Yoona' pada WHERE clause.
Tetapi bagaimana jika formulanya kompleks seperti :
case when ctr.slrid =
 (select mbrcompany from CTRBOOK where ctrbook.CTRBOOKID = ctr.CTRBOOKID)
 then (select mbrshortname from jcmbr jc where MBRID = ctr.byrid)
 else (select mbrshortname from jcmbr jc where MBRID = ctr.slrid) end as 'counterparty'


Ada 2 cara:
1. Dengan melakukan aliasing table pada formula:

SELECT FullName
FROM  (
select concat(FirstName, LastName) as FullName
from Employees
) as sub
WHERE sub.FullName = 'Im Yoona'

2. Membuat View

CREATE VIEW dbo.EmployeesConcat
AS
    SELECT
        concat(FirstName,LastName) as FullName
        FROM Employees

-- maka sql statementnya menjadi lebih singkat
 
SELECT
    FullName
    FROM EmployeesConcat
    WHERE FullName = 'Im Yoona'

Untuk cara lainnya mungkin ada tapi belum dicari lagi, dan kiranya bisa kasih masukkan jika ada :)


Jika ada salah, mohon koreksinya :) Maksud hati hanya mao sharing yang saya tau kok :D

SQL Sequence / Auto Increments (Sample Code)

For documentation purposed.

So, I can open this article and being remembered again :)

For update increment number, such as line number, counter, etc. into table column "Counter" :

DECLARE @i As int

SET @i = 1

UPDATE USERS

SET @i= Counter = @i+1

For update table, in sequence (example):

SELECT counter = 0, * FROM Users INTO #tmp_Users
Should be
SELECT counter = 0, * INTO #tmp_Users FROM Users

 DECLARE @counter int
SET @counter = 0
UPDATE #tmp_Users
SET @counter = counter = @counter + 1

where "counter" is a column name in table "#tmp_user"

Doing the same (on the above) thing with the other method in SELECT statement :
We want something like this:
SELECT {increment value} as Counter, strUsername FROM Users

Counter   strUsername
----------------------
1             John
2             Mike
3             Laura
----------------------

Here is the solution:

CREATE TABLE #user
(
UserID int,
Username varchar(255)
)

INSERT INTO #user VALUES (10, 'John')
INSERT INTO #user VALUES (20, 'Mike')
INSERT INTO #user VALUES (30, 'Laura')

SELECT (
SELECT COUNT(UserID) + 1 AS Counter
FROM #user usr
WHERE usr.UserID < #user.UserID
) AS Counter, Username
FROM #user

For more information, we can all browse in here:

SQL SEQUENCE/AUTO INCREMENTS: RECOMMENDED ARTICLE/TUTOR
CLICK HERE :

Thanks.
Is there any wrong statement, reference or anything else in this article, I'm sorry and please let me know.
I just want to share what I knew :D

SQL Join Revealed - Part 2 -

Continued from Part 1

Belajar dari pengalaman, saya menyadari ada kesalahan konsep tentang Left, Right Inner dan Outer yang selama ini saya yakini berbeda. Namun ternyata, Left Join dan Right Join merupakan bagian dari Outer Join :)

Seperti yang pernah dijelaskan sebelumnya,
Inner = analogi intersect pada diagram venn. (A n B).

Outer = analogi union pada diagram venn. (A U B).

Berikut adalah ilustrasi detailnya:

UNION (A U B) menganalogikan FULL [OUTER] JOIN.
INTERSECTION (A n B) menganalogikan INNER JOIN.
DIFERENCIA (A-B) menganalogikan RIGHT [OUTER] JOIN.
DIFERENCIA (B-A) menganalogikan LEFT [OUTER] JOIN.

Untuk melakukan join 3 atau lebih tabel, dapat dilakukan dengan sintaks WHERE atau dengan JOIN.
ex.

SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;

ex.

SELECT lastname, firstname, tag, open_weekends
FROM drivers, vehicles, locations
WHERE drivers.location = vehicles.location
AND vehicles.location = locations.location
AND locations.open_weekends = 'Yes'

Jika ada salah, mohon koreksinya :)
Maksud hati hanya mao sharing yang saya tau kok :D

SQL Join Revealed -Part 1-

Sedikit catatan SQL Join karena sudah lama gak pegang SQL, biar gak lupa aja.
Ada 2 jenis Join dalam SQL:
1. Inner Join
2. Outer Join

Join / inner join itu kalo kita belajar diagram venn analoginya seperti intersect.
Outer join analoginya seperti Union / gabungan.
Dibedakan menjadi 3 macam :
Biar mudah, saya masukkan juga contoh output dan sample tablenya :)

 

- Left Join
Output semua isi table kiri yang dijoin, sekalipun tidak ada key/PK yang sama/cocok di table kanannya.


- Right Join
Output isi table kanan yang dijoin, sekalipun tidak ada key/PK yang sama/cocok di table kirinya.


- Full Join
Menggabungkan/output isi kedua table sekalipun tidak ada key/PK yang sama/cocok).


Setiap relasi/hubungan antar key yang di join tidak mempunyai pasangannya, maka akan di replace dengan NULL.

Belakangan ini saya juga menemukan ada 2 jenis inner join yang baru bagi saya.
Yaitu:
- Natural Join
Menggabungkan/output isi kedua table berdasarkan nama kolom yang sama tanpa suatu kondisi(qualifier).
Menurut para pengamat, natural join sangat disarankan tidak digunakan.
 

- Cross Join
Menggabungkan/output isi kedua table secara permutasi (Cartesian Products)
Dengan kata lain, cross join akan menghubungkan tiap-tiap kolom table A dengan masing-masing kolom di table B tanpa memperhatikan PK.



Contoh sintaks:
Ket: Khusus untuk inner join bisa di tuliskan secara implisit.
Inner Join

SELECT *
  FROM employee INNER JOIN department
    ON employee.DepartmentID = department.DepartmentID;

Secara emplisit:

SELECT *
  FROM employee, department
    WHERE employee.DepartmentID = department.DepartmentID;

Natural Join

SELECT *
  FROM employee NATURAL JOIN department;

Cross Join

SELECT *
  FROM employee CROSS JOIN department;

Ket: sintaks "Outer" sifatnya optional. Lebih disarankan tanpa kata "Outer".

Right Outer Join

SELECT *
  FROM employee RIGHT OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;

Left Outer Join

SELECT *
  FROM employee LEFT OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;

Full Outer Join

SELECT *
  FROM employee FULL OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;

Dalam oracle, terdapat alternatif penulisan left join secara singkat, sbb:

Left Join

SELECT *
  FROM employee, department
    WHERE employee.DepartmentID = department.DepartmentID (+);

Namun sangat tidak disarankan karena tidak menganut standard ANSI. Dan dapat menjadi ambigu.

Dalam Sysbase juga terdapat alternatif penulisan, yaitu dengan menambahkan "*"

SELECT *
  FROM employee, department
    WHERE employee.DepartmentID *= department.DepartmentID ;

Setiap database (Oracle, MSSQL, MySql, PostgreSQL, DB2, dll) memiliki implementasi INNER dan OUTER JOIN yang berbeda-beda. Ada yang tidak memiliki natural join, ada juga yang memiliki. Ada yang memiliki penyingkatan penulisan left dan right join, ada yang tidak.

Kalo ada yang salah, mohon beri comment untuk revisinya ya :D

SQL Case Statement / If Else Statement

Belakangan ini baru dapet tes SQL sederhana tapi sayangnya saya sama sekali tidak mudeng / blank dan tidak mahir dalam SQL. Hal dikarenakan terlalu jarang menggunakan SQL dalam bidang pekerjaan. Hanya query-query sangat sederhana saja, alhasil query sederhana seperti IF-ELSE statement standard dalam SQL saja saya lupa :(

Berikut catatan pribadi saya yang diambil dari sumber-sumber di internet:

Saya ingin mengambil result/show column yang hasilnya tergantung pada isi table yang bersangkutan.
Misal:
  - Ingin menampilkan kolom "TipeHarga" dengan nilai yang diperoleh dari "Harga" yang terdapat pada table.
- Jika "Harga" > 1000 maka isi kolom "TipeHarga" = Mahal , jika tidak, maka "TipeHarga" = Murah.


Lewat SQL, hal ini bisa diperoleh dengan kata kunci case , when-then-else.

CASE [expression]
    WHEN [value | Boolean expression] THEN [return value]
    [ELSE [return value]]
END

Contoh:

SELECT CASE WHEN Harga >= 1000
            THEN 'Mahal'
            ELSE 'Murah'          END as TipeHarga
  From X

Maka yang tampil adalah kolom "TipeHarga" untuk masing-masing "Harga" < 1000 maka isinya adalah 'Murah', sebaliknya > 1000 isinya 'Mahal'

Dapat juga seperti ini:

SELECT CASE Harga
                WHEN 1 Then '1 Rupiah'
                WHEN 1000 Then '1000 Rupiah'
                WHEN 5000 Then '5000 Rupiah'
                ELSE 'Tidak Diketahui'
END  as 'Status' 
From X

Contoh query lengkapnya :

select P.Name

     , P.Type

     , PS.SalePrice as Price

     , case when PS.SalePrice > 200

            then 'Expensive'

            else 'Value' end AS PriceType

  from Product P

inner

  join ProductSale PS

    on P.ProductID = PS.ProductID

 where P.Category = @Category

   and P.Vendor = @Vendor

order by P.Name desc

Maaf kalo ada salah, cuman mao sharing yang saya tau :p
Saran dan Kritik sangat diharapkan :)