-2

I am new to SQL and I am trying to create a query that will give me the same result as LEFT OUTER JOIN but between two data bases. For example, in DB1 there are 100 records with the value X in specific column and in DB2 there 80 records. I want a query that will give the 20 missing records. Thanks.

Gal Shai
  • 11
  • 1
  • 4
    Databases do not have records in columns. Databases have schemas. Schemas have tables. Tables have columns. – Gordon Linoff Jun 27 '17 at 13:54
  • ok, I phrased it incorrect. I am looking for missing records, meaning the rows in the table that have the value X in column Y. – Gal Shai Jun 27 '17 at 13:58
  • @GordonLinoff Is there any difference between Schemas and Tables? – Ankit Bajpai Jun 27 '17 at 13:58
  • @AnkitBajpai A schema contains/defines the tables and other objects and how they relate to each other – JohnHC Jun 27 '17 at 14:00
  • no, there is no difference in terms of "structure". one DB is for Dev environment and the other is for Test environment. – Gal Shai Jun 27 '17 at 14:00
  • To query across two databases in Oracle you need a [database link](https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205). I am not sure what what kind of function you are looking for though. – William Robertson Jun 28 '17 at 15:32

2 Answers2

0

If you mean "between two tables":

select Table1.*
from Table1
left join Table2
on Table1.id = Table2.id
where Table2.id is null

This gives everything in Table2 that does not match

For more visual info on joins, go here

For basic info on SQL, try here

JohnHC
  • 10,935
  • 1
  • 24
  • 40
  • @gaishai - if your answer is different to the posted answers please add it as a response to your own question. Why not contribute to the store of SO wisdom? – APC Jul 05 '17 at 19:15
0
DB1 (table_users)
+----+-----------+--------------+
| id | username  |    lastname  |
+----+-----------+--------------+
| 1  | xxxxxx    | mmmmmmm      |
+----+-----------+--------------+
| 2  | bbbbbb    | wwwwwww      |
+----+-----------+--------------+



DB2 (table_users)
+----+-----------+--------------+
| id | username  |    lastname  |
+----+-----------+--------------+
| 1  | xxxxxx    | mmmmmmm      |
+----+-----------+--------------+
| 2  | bbbbbb    |  wwwwwwww    |
+----+-----------+--------------+
| 3  | ccccc     | ppppppppppp  |
+----+-----------+--------------+



SELECT  *
FROM    DB2.table_users
WHERE   phone_number NOT IN (SELECT * FROM DB2.table_users);

I think it servers the same purpose, except that your dealing with tables from different databases.

MelB
  • 11
  • 5
  • Not in is not a good option in case no records come back, `not in (null) `... Far better to use `where not exists` – JohnHC Jun 27 '17 at 14:34