{"id":94,"date":"2011-09-09T15:34:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=94"},"modified":"2011-09-09T15:34:00","modified_gmt":"1970-01-01T00:00:00","slug":"tsql-ile-iki-tablonun-kayitlarinin-karsilastirilmasi","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2011\/09\/09\/tsql-ile-iki-tablonun-kayitlarinin-karsilastirilmasi\/","title":{"rendered":"TSQL ile Iki Tablonun Kayitlarinin Karsilastirilmasi"},"content":{"rendered":"\n

TSQL ile iki tablonun karsilastirilmasi : <\/h3>\n

SQL Server da iki tablo arasindaki farklarin ve benzerliklerin bulunabilmesi i\u00e7in EXCEPT ve INTERSECT anahtar kelimeleri kullanilir, ayni islemi yapabilmek i\u00e7in join ifadelerindende faydalanilabilir. Bu makalemizin genel amaci EXCEPT ve INTERSECT anahtar s\u00f6zc\u00fckleri olacaktir.<\/p>\n\n\n\n

EXCEPT anahtar s\u00f6zc\u00fcg\u00fcn\u00fcn kullanim sablonu :<\/h4>\n\n
\n-- SQL Server T-SQL iki tabloyu karsilastirmak.
\n
\nSELECT
\nEtiket='Tablo1 de bulunan fakat Tablo2 de bulunmayan kayitlar',* \n
FROM
\n(
\n  SELECT * FROM Tablo1
\n    EXCEPT
\n   SELECT * FROM Tablo2
\n ) x
\nUNION ALL
\nSELECT
\nEtiket='Tablo2 de bulunan fakat Tablo1 de bulunmayan kayitlar',*
\nFROM
\n(
\n  SELECT * FROM Tablo2
\n   EXCEPT
\n  SELECT * FROM Tablo1
\n) y
\n<\/div>\n\n<\/code>\n

\nMakaledeki \u00f6rneklerimizde kullacagimiz tablolar i\u00e7in asagidaki scripti kullanabilirsiniz.\n<\/p>\n.<\/a>\n\n

\ncreate table Urun2010\n(\n\tID int ,\n\tUrunAdi varchar(50),\n\tMarka varchar(30)\n)\ninsert into Urun2010 (ID,UrunAdi,Marka) values \n(1,'L2208 LCD monitor','HP'),\n(2,'EliteBook 6930p Notebook','HP'),\n(4,'Omnia lite Cep Telefonu','Samsung'),\n(6,'JetStream Pilot Kalem','Uni'),\n(7,'Galaxy II','Samsung')\n\ncreate table Urun2011\n(\n\tID int ,\n\tUrunAdi varchar(50),\n\tMarka varchar(30)\n)\ninsert into Urun2011 (ID,UrunAdi,Marka) values \n(1,'L2208 LCD monitor','HP'),\n(3,'E71 Cep Telefonu','Nokia'),\n(4,'Ominia lite Cep Telefonu','Samsung'),\n(5,'Ip Phone 7911','Cisco'),\n(6,'JetStream Pilot Kalem','\n<\/div>\n<\/code>\n

Urun2010 ve Urun2011 tablolarini karsilastiralim. <\/h4>\n\n
\nSELECT Etiket='Urun2011 de bulunan, Urun2010 da bulunmayan',* FROM\n (SELECT * FROM Urun2010\n  EXCEPT\n SELECT * FROM Urun2011) x\nUNION ALL\nSELECT Etiket='Urun2010 da bulunan, Urun2011 de bulunmayan',* FROM\n (SELECT * FROM Urun2011\n   EXCEPT\n SELECT * FROM Urun2010) y\n--Sonu\u00e7 : (4 row(s) affected\n<\/div>\n<\/code>\n

\nYukaridaki sorgu ile Urun2010 ve Urun2011 tablolarini kayit bazinda karsilastirdik, b\u00f6ylece birinde olup digerinde olmayan kayitlari tespit ettik.\n<\/p>\n

Her iki tabloda da olan kayitlari tespit etmek i\u00e7in INTERSECT anahtar s\u00f6zc\u00fcg\u00fcn\u00fcn kullanimi:<\/h4>\n\n
\n-- Her iki tablodada ortak olarak bulunan kayitlari bulalim\nSELECT * FROM Urun2011\n  INTERSECT\nSELECT * FROM Urun2010\n

\n-- Sonu\u00e7 : (3 row(s) affected)\n<\/div>\n<\/code>\n

Tablolar arasindaki kayit farkliliklarini bulmak i\u00e7in NOT EXIST veya LEFT JOIN ifadelerinide kullanabiliriz.<\/p>\n.<\/a>\n\n

\n-- Urun2011 de olup Urun2010 da olmayan \u00fcr\u00fcnleri bulmanin alternatif y\u00f6ntemler, (NOT EXIST ve LEFT JOIN)\n \n \n SELECT * FROM Urun2011 p1WHERE \n NOT EXISTS \n (\n  SELECT * FROM Urun2010 p2\n WHERE p2.ID = p1.ID\n)\n-- (2 row(s) affected)\n<\/div>\n<\/code>\n

LEFT JOIN y\u00f6ntemi (Ayni sonu\u00e7 \u00fcretir)<\/h4>\n\n
\nSELECT * FROM Urun2011 p1\n LEFT OUTER JOIN Urun2010 p2\n   ON p2.ID = p1.ID\nWHERE p2.ID IS NULL\n-- (2 row(s) affected)\n<\/div>\n<\/code>\n.<\/a>","protected":false},"excerpt":{"rendered":"

SQL Server da iki tablo arasindaki farklarin ve benzerliklerin bulunabilmesi için EXCEPT ve INTERSECT anahtar kelimeleri kullanilir, ayni islemi yapabilmek için join ifadelerindende faydalanilabilir. Bu makalemizin genel amaci EXCEPT ve INTERSECT anahtar sözcükleri olacaktir.<\/p>\n","protected":false},"author":517,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[422],"tags":[4482,5641,5779,6021,5043,5222,4294],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/94"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/517"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=94"}],"version-history":[{"count":0,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/94\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=94"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=94"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=94"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}