Friday 18 March 2011

MINUS IN ORACLE AND OUTER JOINS IN ORACLE

To see the different try the following:

DROP TABLE FOO;
DROP TABLE BAR;

CREATE TABLE FOO(
FOO_ID NUMBER,
FOO_NAME VARCHAR(20)
);


CREATE TABLE BAR(
BAR_ID NUMBER,
BAR_NAME VARCHAR(20)
);

INSERT INTO FOO VALUES(1,'Fawad');
INSERT INTO FOO VALUES(2,'Nazir');

INSERT INTO BAR VALUES(1,'Fawad');
INSERT INTO BAR VALUES(2,'Nazir');
INSERT INTO BAR VALUES(3,'Hello');
INSERT INTO BAR VALUES(4,'World');

-- Minus
SELECT BAR_ID FROM BAR
MINUS
SELECT FOO_ID FROM FOO

-- Right outer join
SELECT BAR_ID FROM FOO F RIGHT JOIN BAR B ON F.FOO_ID = B.BAR_ID WHERE FOO_ID IS NULL AND FOO_NAME IS NULL

In this case Both of the above queries give same results.

No comments:

Post a Comment

Azure OpenAI Architecture Patterns & Deployment Patterns

Sharing some useful links that will help customers architect Azure OpenAI solution using the best practices: (1) Azure OpenAI Landing Zone r...