Correct Code to select and compare

Navigation
0

I want to select * from table A that are different from table B and I have used the following code: 

select * from Tabela A inner join Tabela B on A1 != B1 and A2 != B2 and A3 != B3

But the computer's memory goes to the limit and blocks the PC.

Table A 

ID  A1  A2  A3  A4  A5
1   1   2   3   4   XPTO1
2   2   3   4   5   XPTO2
3   3   4   5   6   XPTO3
4   4   5   6   7   XPTO4
5   5   6   7   8   XPTO5
6   6   7   8   9   XPTO6
7   7   8   9   10  XPTO7
8   8   9   10  11  XPTO8
9   9   10  11  12  XPTO9
10  10  11  12  13  XPTO10
11  11  12  13  14  XPTO11
12  12  13  14  15  XPTO12
13  13  14  15  16  XPTO13
14  14  15  16  17  XPTO14
15  15  16  17  18  XPTO15
16  16  17  18  19  XPTO16
17  17  18  19  20  XPTO17
18  18  19  20  21  XPTO18
19  19  20  21  22  XPTO19
20  20  21  22  23  XPTO20

Table B 

ID  B1  B2  B3
1   1   2   3
2   5   6   7
3   9   10  11
4   13  14  15
5   17  18  19
    
asked by anonymous 26.02.2018 / 12:45

2 answers

1

Use the EXISTS clause.

In the specific case of your example: 

SELECT * FROM A
WHERE NOT EXISTS (
  SELECT * FROM B
  WHERE A1 = B1
  AND A2 = B2
  AND A3 = B3)

I've created a DB Fiddle to see how it works and test if it's the result you expect.

    
26.02.2018 / 13:06
1

You can do this: 

SELECT a.*
FROM 'Tabela A' AS a
WHERE NOT EXISTS
(SELECT 'b.ID'
 FROM 'Tabela B' AS b
 WHERE b.B1 = a.A1
 AND b.B2 = a.A2
 AND b.B3 = a.A3);

You make a sub-query that selects all items in table B that are the same as items in Table A, if the row in Table A does not exist in the result of this sub-query, that row only exists in Table A.

Or so: 

SELECT *
FROM 'Tabela A'
WHERE (A1, A2, A3) NOT IN
(SELECT B1, B2, B3
 FROM 'Tabela B');

This last one I saw now in this link, which has a third example with LEFT OUTER JOIN , which article says to be the most efficient, but I think it is not useful for you, because you need all the fields of the Table A :

  

Union, Difference, Intersection in MySQL
link

    
26.02.2018 / 13:14
How to do @media print skip the @media max-width Variables and structure of flow control if in Java