iT邦幫忙

9

趣味SQL 不出現等號(=) 的查詢方式

create table it191211a (
  id int not null primary key
);

create table it191211b (
  id int not null primary key
);

insert into it191211a values
(1),(2),(3),(4),(5);

insert into it191211b
select id + 2
  from it191211a;

select a.id
  from it191211b b
     , it191211a a
 where a.id = b.id;

+----+
| id |
+----+
|  3 |
|  4 |
|  5 |
+----+
(3 rows)

若查詢的SQL Command 不出現等號 (=) , 能有哪些查詢方式呢??

感謝大家提供了許多答案,就不重複大家已經寫出來的了.
在此補充一個.

 select a.id
   from it191211b b 
natural join 
        it191211a a;
+----+
| id |
+----+
|  3 |
|  4 |
|  5 |
+----+
(3 rows)
natural join
第一次看到。
剛查了一下,原來還可以這樣用。長知識了
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
4

我唯一能想到的第一個方式就是in了。

其它方式還想不太到。
not雖然也是個方式,不過總結也是用in的方式。

啊,剛還有想到一招是用if的方式。不過這可能要試試就是了。

試了一下,用跑漏洞的方式

SELECT * FROM `db` where if(id>0 AND id<2,true,false)

這樣也是沒用到=。不過有點偷吃步的感覺

剛沒注意到要用上面的範例。如果是兩張表的話。因該有很多用法才對

SELECT * FROM `it191211a` join it191211b USING(id)
SELECT * FROM `it191211a` WHERE id IN (SELECT id FROM it191211b)

有些想法雖然可以達到效果,但考量效能問題就不用了。如 union 的方式

skl iT邦新手 5 級 ‧ 2019-12-11 15:44:40 檢舉

join 的話也可以用 left/right join

SELECT a.id
FROM it191211a AS a
LEFT JOIN it191211b AS b USING(id)
WHERE b.id IS NOT NULL
5
純真的人
iT邦大師 1 級 ‧ 2019-12-11 13:13:21

恩?不用等號的查詢方式?

like charindex 印象都可以用@@a

select a.id
  from it191211b b
     , it191211a a
 where a.id like b.id;
 
 select a.id
from it191211b b
     , it191211a a
where charindex(',' + Convert(varchar,a.id) + ',' , ',' + Convert(varchar,b.id) + ',') > 0;

還有這個算不算..

select a.id
  from it191211b b
     , it191211a a
where not (a.id <> b.id);

對喔,not搭配<>也是一招。
沒去想到,殘念。

2
暐翰
iT邦大師 1 級 ‧ 2019-12-11 13:18:55

沒想過的問題,感謝大大提供給大家思考
個人目前想到的方式 :

union + in : 模擬inner join

select id
from it191211b
where id in (select id from it191211a)
union
select id
from it191211a
where id in (select id from it191211b)

inner join + using

select a.id
from it191211b b 
inner join it191211a a using (id)

線上測試連結 here

相除 = 1 這個有用到 = 號了。不算喔!!

暐翰 iT邦大師 1 級 ‧ 2019-12-11 14:14:07 檢舉

對耶 XD

6
allenlwh
iT邦高手 1 級 ‧ 2019-12-11 13:34:00

交集用法: INTERSECT
從兩個資料表取出同時存在的記錄。

SELECT id FROM it191211a 
INTERSECT
SELECT id FROM it191211b 
看更多先前的回應...收起先前的回應...
暐翰 iT邦大師 1 級 ‧ 2019-12-11 13:41:50 檢舉

這個讚 !

allenlwh iT邦高手 1 級 ‧ 2019-12-11 13:43:42 檢舉

謝謝。剛好前陣子看到一篇文章提到,我才知道有這個語法。

INTERSECT 這還真的沒用過。

allenlwh iT邦高手 1 級 ‧ 2019-12-11 13:53:14 檢舉

補充另一個語法:
差集用法: EXCEPT
只取出A資料表中不包含與B資料表內相同的資料。

SELECT id FROM it191211a as A
EXCEPT
SELECT id FROM it191211b as B

這可能還是需要比較一下效能的差異。畢竟用in的方式也是可以做到這樣的事。
但如果有這樣的新語法出來。(是新的語法嘛??我不確定)
理論上因該要比用in還要好才對。

allenlwh iT邦高手 1 級 ‧ 2019-12-11 16:51:15 檢舉

我上次看到的文章是提到說,使用in,執行效率會不好。
我曾經有過一段語法,使用in進行查詢(非常大量的查詢),大約要13-15秒才有回應。
至於使用in的執行效率是否真的不好(也有可能是我的查詢語法寫的不好),可能要請教各位大神了。

in(小表)的效率比較好。但如果是大表就不建議用in
一般大表比較佳的方式用join或是子查尋處理過再做處理。

allenlwh iT邦研究生 3 級 ‧ 2019-12-11 13:43:42

謝謝。剛好前陣子看到一篇文章提到,我才知道有這個語法。

https://ithelp.ithome.com.tw/articles/10229333

是我寫的這篇嗎?

allenlwh iT邦高手 1 級 ‧ 2019-12-12 08:18:04 檢舉

/images/emoticon/emoticon01.gif
抱歉,不是。

thwu iT邦新手 5 級 ‧ 2019-12-12 09:16:18 檢舉

我在一些情況下用了不少 EXCEPT,跟大家分享一下我用的經驗。

它除了可以多欄位比較,還可以不指定欄位。
如果使用 in 或是 exists,則必須指定欄位。
EXCEPT 可以這樣用

SELECT * FROM TableA
EXCEPT
SELECT * FROM TableB

使用的情境是做單向資料複寫
因為要注意的是它是以 TableA 來判斷與 TableB 不同的資料,換句話說是:找出存在 TableA 之中,但不存在 TableB 之中的資料。

如果是

SELECT * FROM TableB
EXCEPT
SELECT * FROM TableA

就是:找出存在 TableB 之中,但不存在 TableA 之中的資料。

範列:(借用原PO的範列)

DECLARE @TableA table(
  id INT NOT NULL PRIMARY KEY
);

DECLARE @TableB table(
  id INT NOT NULL PRIMARY KEY
);

INSERT INTO @TableA
VALUES (1),(2),(3),(4),(5);

INSERT INTO @TableB
SELECT id + 2
FROM @TableA;
SELECT * FROM @TableA;

id
.....
1
2
3
4
5

SELECT * FROM @TableB;

id
.....
3
4
5
6
7

SELECT * FROM @TableA
EXCEPT
SELECT * FROM @TableB

id
.....
1
2

SELECT * FROM @TableB
EXCEPT
SELECT * FROM @TableA

id
.....
6
7

SELECT * FROM @TableB
INTERSECT
SELECT * FROM @TableA

id
.....
3
4
5

allenlwh iT邦高手 1 級 ‧ 2019-12-12 13:18:06 檢舉

thwu 如果 tableA 和 tableB的Schema不相同,好像就不能使用了

//員工基本資料
create table Emp (
  id int not null primary key,
  name varchar(50) null
);

//禮金領取記錄
create table Emp_Gift (
  id varchar(10) not null primary key,
  pickdatetime datetime
);

insert into Emp values ('1','張三')
insert into Emp values ('2','李四')
insert into Emp values ('3','王五')

insert into Emp_Gift values ('2',getdate())
 
//查詢未領取禮金的員工
select id,name from Emp
 EXCEPT
select id FROM Emp_Gift
使用 UNION、INTERSECT 或 EXCEPT 運算子結合的所有查詢,其目標清單中的運算式數量必須相等。
1
舜~
iT邦高手 1 級 ‧ 2019-12-11 14:13:02

還可以用 regexp 不需要等號
(regexp不使用索引)

話說...為什麼不能用等號??


in<>likeregexp
union + ininner join + using
INTERSECTEXCEPT

就算趣味的考題了。
這樣子玩也不錯啊。

0
ckp6250
iT邦好手 1 級 ‧ 2019-12-11 15:32:53

我的最沒效率,又臭又長,但為了符合題旨,又要與眾不同,只有出此下策。

SELECT id	
FROM (
	SELECT id, 'a' AS yyy FROM it191211a 
	UNION SELECT id, 'b' AS yyy FROM it191211b
) c 
GROUP BY id 
HAVING length(group_concat( DISTINCT yyy ))>1
看更多先前的回應...收起先前的回應...

/images/emoticon/emoticon14.gif

skl iT邦新手 5 級 ‧ 2019-12-11 15:55:57 檢舉

having 後用 count 就可以了,
用到 length, group_concat, DISTINCT 一定是故意的! XD

ckp6250 iT邦好手 1 級 ‧ 2019-12-11 16:08:33 檢舉

  我們等樓主頒獎,我打算角逐【最佳劇組獎】我的道具最多,聽說是4K電視機一台。

哥哥你用的奇技淫巧,是看了什麼奇怪的東東...

SELECT id	
  FROM (SELECT id
             , 'a' as yyy
          FROM it191211a 
         UNION 
        SELECT id
             , 'b' as yyy
          FROM it191211b
        ) c 
 GROUP BY id
HAVING cardinality(array_agg(distinct yyy)) > 1;

+----+
| id |
+----+
|  3 |
|  4 |
|  5 |
+----+
(3 rows)
ckp6250 iT邦好手 1 級 ‧ 2019-12-12 04:55:06 檢舉

我是想說,多塞一點指令進去,顯得了比較有學問,您也沒有要求要有效能。/images/emoticon/emoticon15.gif

我要發表回答

立即登入回答