CODEWITHSUNDEEP
sqlhadoop
nightowl92
nightowl92

Reputation: 1

Select records from a table, which don't exist in another table whilst keep records from one table where there are multiple

I have 2 tables TABLE1 and TABLE2 in hadoop imparla. TABLE 2 has some of the records of the TABLE1. What I want is to select all the records from TABLE 1, which don't exist in TABLE2 and then combine so I end up with a table containing all the records from TABLE2 and records from TABLE1 that are not in TABLE2 So I coded:

String sq = "SELECT TABLE1.name, TABLE1.surname, TABLE1.id FROM TABLE1"
            + "LEFT JOIN TABLE1"
            + "ON TABLE1.id <> TABLE2.id";

Upvotes: 0

Views: 95

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You should check with where matching value is null in table2

String sq = "SELECT TABLE1.name, TABLE1.surname, TABLE1.id FROM TABLE1"
        + "LEFT JOIN TABLE2"
        + "ON TABLE1.id = TABLE2.id"
        + "WHETE TABLE2.id IS NULL ";

Upvotes: 0

Related Questions