Post Page Advertisement [Top]

SQL JOINS

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Look at the "Persons" table:

P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:

O_Id
OrderNo
P_Id
1
77895
3
2
44678
3
3
22456
1
4
24562
1
5
34764
15
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs
Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.
  • JOIN: Return rows when there is at least one match in both tables
  • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
  • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
  • FULL JOIN: Return rows when there is a match in one of the tables


USE YazOkulu
--inner join
select Ad,Soyadi,RolAdi
from Kullanici INNER JOIN Rol
on
Kullanici.RolId=Rol.ID

--join yapmadan
--select Ad,Soyadi,RolAdi from Rol,Kullanici where Kullanici.RolId=Rol.ID

USE YazOkulu
--outer join
select Urun.Ad,Kategori.Ad
from Urun left outer join Kategori
on
Urun.KategoriId=Kategori.ID

--outer join
select Urun.Ad,Kategori.Ad
from Urun right outer join Kategori
on
Urun.KategoriId=Kategori.ID


--full outer join
select Urun.Ad,Kategori.Ad
from Urun full outer join Kategori
on
Urun.KategoriId=Kategori.ID
*= left outer joine denktir.
--CROSSS JOIN
--İki tablonun kartezyen çarpimini oluşturur.
select *from Urun
cross join Kategori

--cross join yerine
select * from Urun,Kategori

AS

--kolonun isminde deðþikilik yapmak için as kullanýlýr
select Urun.Ad as 'Ürünün adý',Kategori.Ad
from Urun left outer join Kategori
on
Urun.KategoriId=Kategori.ID


BOLEAN OPERATORS

---boolean operatorler
--NOT
--AND
--OR
select Ad from Kullanici where Ad !='a' --buyuk küçük harf duyarlilği yok

select Ad from Kullanici where Ad <>'a' --buyuk küçük harf d duyarlilği var
AND & OR Operators

The AND & OR operators are used to filter records based on more than one condition.
The AND operator displays a record if both the first condition and the second condition is true.
The OR operator displays a record if either the first condition or the second condition is true.

AND Operator Example
The "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to "Svendson":
We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'
The result-set will look like this:
P_Id
LastName
FirstName
Address
City
2
Svendson
Tove
Borgvn 23
Sandnes


OR Operator Example
Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to "Ola":
We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola'
The result-set will look like this:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes


Combining AND & OR
You can also combine AND and OR (use parenthesis to form complex expressions).
Now we want to select only the persons with the last name equal to "Svendson" AND the first name equal to "Tove" OR to "Ola":
We use the following SELECT statement:

SELECT * FROM Persons WHERE
LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')
The result-set will look like this:
P_Id
LastName
FirstName
Address
City
2
Svendson
Tove
Borgvn 23
Sandnes

BETWEEN

Select * from Urun where Ad between '.' and ','
--between istediÄŸimiz  iki deÄŸer arasi sonuçlari geri döndürür.
LIKE

--Like operatoru
      --: % kaç karakter ve hangi karakter olursa olsun anlamındadır.
      --: _ ise tek bir karakteri temsil eder.
      --:[a-z] a ile z arasindakilerden bir deÄŸer geri döndürür
      --:[ab] a ya da b olanları geri döndür.

      --:soru:ikinci karkter a ve son karakteri b olani bul.
      select *from Urun where Ad like '_a%b'
     
      --örnek:son harfi a olan veriler:
      select *from Urun where Ad like '%a'

      --örnek:ilk harfi a ya da b olan veriler:
      select *from Urun where Ad like '[ab]%'
     
      --örnek:ilk harfi a ya da b olmayan veriler:
      select *from Urun where Ad like '[^ab]%'

      --örnek:tüm veriler:
      select *from Urun where Ad like '%' -- null geri donmedi
     
     --örnek:ilk harfi b-z arasında olmayan veriler:
      select *from Urun where Ad like '[^b-z]%'
     

ORDER BY

--ORDER BY Ada göre siralar(default olarak azalan sirdada(asc), desc yazilirsa artan sirada yazar)
select * from ParcaSiparis order by ParcaSiparis.ParcaId desc
TOP

Sorgudan belirtilen sayıda en baştaki kayıtları görüntüler.
select top 2 * from ParcaSiparis order by ParcaSiparis.ParcaId asc
AGGREGATE FUNCTIONS IN SQL

--AVG:Sutunun Ortalamasini alir
--COUNT:Kayit sayar
--MAX ve MIN Sutundaki en buyuk ve en küçük deðerleri geri döndürür
--SUM: Sutunun toplamasini alir
--HAVING :Satilari gruplarken şart koşar çünkü aggregate functionlar kullanilacaksa where koşulu kullanilamaz.
SQL COUNT(column_name) Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to count the number of orders from "Customer Nilsen".
We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'
The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:
CustomerNilsen
2




DISTINCT

--DISTINCT: tekrarli satirlari göstermez.
INSERT,DELETE,UPDATE

--INSERT INTO <table_name> (stun1,stn2) values (deger1,deger2) tabloya yeni satir ekler.

insert into Urun(KategoriId,Ad)
values (2,1,'Coca-Cola')

-- UPDATE <table_name>  tabloyu günceller.
--SET sutun1=deger1,sutun2=deger2


Update Urun set KategoriId=3,MarkaId=2 where ID=3


---DELETE FROM<table_name> tablodan satir siler.
--WHERE alanad=deger
delete from Urun where ID=3

Aslında databaselerde update işlemi yoktur Update işlemi delete ve insert işlemlerinin ard arda sıralanmasından oluşur.

Hiç yorum yok:

Yorum Gönder

Bottom Ad [Post Page]