1. Non-conventional implementation of collection class in http://www.blogjava.net/rox/archive/2006/09/01/67008.htmlmysql
MySQL only supports Union (Union) set operations. It seems that it is only available after 4.0;
However, the intersection intersect and difference set except T are not implemented.
The general solution uses in and not in. A small amount of data is acceptable, but the efficiency is low when the data volume is large.
In fact, union can be used to implement the other two operations. Of course, there is no way.
Difference set limit t:
Select ID from (
Select distinct A. Aid as ID from table_a
Union all
Select distinct B. bid as ID from table_ B B
) Temp group by ID having count (ID) = 1
Intersection intersect:
Select ID from (
Select distinct A. Aid as ID from table_a
Union all
Select distinct B. bid as ID from table_ B B
) Temp group by ID having count (ID) = 2
However, the functions of the above methods are also limited,
It can only be used to check whether an ID exists in tables A and B,
Or it only exists in one of the tables A and B,
The ID cannot be checked multiple times in a table.
In addition, the difference set has different order. No difference is found here.
2. http://hi.baidu.com/truetruelove/blog/item/f0fda8441bf22048510ffeba.html
1. Evaluate the two table difference sets. ewb_t_books is a book table, and ewb_t_title is a book classification table, which is connected through bt_titleid (not null. Find books whose category does not exist A. subquery uses not in # Explain extended Select book_id, bt_titleid From ewb_t_books B Where bt_titleid not in (select bt_titleid from ewb_t_title) # Show warnings; B. subquery not exists # Explain extended Select book_id, bt_titleid From ewb_t_books B Where not exists (select * From ewb_t_title A where B. bt_titleid = A. bt_titleid) # Show warnings; C. The left join determines that the right table is null. # Explain extended Select book_id, bt_titleid From ewb_t_books left join ewb_t_title B using (bt_titleid) Where isnull (B. bt_titleid) # Show warnings; Conclusion: A simple test shows that not in is the slowest, and the other two are equally colored. Select * from employee where salary <> 3000; You can rewrite this query to not using not: Select * from employee where salary <3000 or salary> 3000; Although the results of these two queries are the same, the second query scheme is faster than the first query scheme. The second query allows the database to use indexes for salary columns, while the first query does not. |