Joininig Table

[johnsonlim026]

Hi,
I am writing a stored procedure and face the following question. f1 is the primary key and foreign key in table A and B accordingly .I am joining both the table in order to get f2 in Table B

Table A Table B
======= ========
f1 f1 f2
------ -------- ----
PK1 FK1 xxx
PK2 FK2 xxx
PK3
PK4

when I write the following code
select A.f1, B.f2
from Table A
inner join table B on A.f1=B.fl

I lost the record in table A for PK3 and PK4.Table A is my main table and no any record should be removed from it.So I would like to set value of f2 in Table B to 0 in my joining table if Table A primary key does not exist in f1 in Table B.

The result I should have is as below

Result
f1 f2
------ ----
PK1 xxx
PK2 xxx
PK3 0
PK4 0

Is it possible to do this?Can anyone give me suggestion to do this?

[aniskhan]

try left join
SELECT Table1.f1, Table2.f2
FROM Table1 LEFT JOIN Table2 ON Table1.f1=Table2.fl

[johnsonlim026]

Thanks aniskhan for giving me the key idea.I have 1 more question.Can I set the null value in f2 to 0 durint the left outer join?

[wildfrog]

Can I set the null value in f2 to 0 durint the left outer join?Maybe you can use the COALESCE (http://msdn2.microsoft.com/en-us/library/ms190349.aspx) function.

- petter

[aniskhan]

try ISNULL and COALESCE functions in sqlServerSELECT title, ISNULL(price, 0.00) AS price
FROM titles
SELECT title, COALESCE(price, 0.00) AS price
FROM titles
In oracle u have the nvl function.
SELECT title, NVL(price, 0.00) AS price
FROM titles

[johnsonlim026]

Thanks wildfrog ,aniskhan.I solve the "set to 0" using ISNULL function.
Thanks a lot for helping me this new bee...

[davide++]

Hi all.

Usually Left/Right joins (or outer-of-joins in Oracle terminology) are resolved using specific SQL statements that depend on database; for example, statement


SELECT Table1.f1, Table2.f2
FROM Table1 LEFT JOIN Table2 ON Table1.f1=Table2.fl


is valid in Access or SqlServer, but one can't using it in Oracle. In oracle one should write


SELECT Table1.f1, Table2.f2
FROM Table1, Table2
WHERE Table1.f1 = Table2.fl (+)


where (+) is out-of-join operator.

A method that seems to be valid for every db is to extract fields of table in "left condition" using a subquery in SELECT fields list of the main query. An example will explain this idea.

Let consider two statements:


SELECT TC.TIPO_CPE, TC.DESCR_CPE, ST.DESCRIZIONE
FROM TIPI_CPE TC, SOTTOTIPI_CPE ST
WHERE TC.TIPO_CPE = ST.TIPO_CPE (+)
AND TC.TIPO_CPE = 37

SELECT TC.TIPO_CPE,
TC.DESCR_CPE,
(SELECT ST.DESCRIZIONE
FROM SOTTOTIPI_CPE ST
WHERE TC.TIPO_CPE = ST.TIPO_CPE
)
FROM TIPI_CPE TC
WHERE TC.TIPO_CPE = 37


They're two equivalent queries: in the second query if the join condition in the subquery isn't satisfied, subquery will return NULL for column DESCRIZIONE.
First statement is Oracle specific, whereas second statement is valid both Oracle and Access. Moreover, second query is faster than first.
There's a restriction: subquery must return only one record; if not, will raise an error.

[karal]

you can use joins according to your requirement but the main thing is concep should be clear.

[davide++]

Well.

Concepts about join are independent of any requirements, because join is a technique related to a specific programming language, SQL; moreover, I believe that this concepts are very clear for me.

In add to what I said, Oracle doesn't allow to put two outer of join in a query; so, using subqueries as fields of a main query in this case become a necessary way.

[cjard]

Note that davide's advice regarding oracle join syntax is only valid up to Oracle 8

Oracle 9i and above supports ANSI join syntax:

a INNER JOIN b
a LEFT OUTER JOIN b
a RIGHT OUTER JOIN b
a FULL OUTER JOIN b
a NATURAL JOIN b
a CROSS JOIN b


I do not recommend you use Oracle 8, or the old style join syntax.

[cjard]

As a further note, it is worth observing that any type of outer join will revert to being an inner join if any of the outer joined tables are present in the where clause if provision for null is not made:

a LEFT OUTER JOIN b
USING(common_col_name) WHERE b.col = 123

a LEFT OUTER JOIN b
ON(b.common_col_name = a.common_col_name) WHERE b.col = 123




In oracle, provision for null can be made in the join clause or the where clause:

a LEFT OUTER JOIN b
ON(b.common_col_name = a.common_col_name AND b.col = 123)

a LEFT OUTER JOIN b
ON(b.common_col_name = a.common_col_name)
WHERE (b.col = 123 OR b.col IS NULL)


I always recommend you write outer joins in the following way:
a LEFT OUTER JOIN b
ON(b.common_col_name = a.common_col_name AND b.col = 123)

i.e. the table you are outer joining comes on the left side of the =
this is because Oracle 9 has a bug whereby constants in the join:


a LEFT OUTER JOIN b
ON(
b.common_col_name = a.common_col_name AND
b.col = 123
)


are ignored if they appear first:


a LEFT OUTER JOIN b
ON(
a.common_col_name = b.common_col_name AND
123 = b.col
)



This bug was later fixed, but if you always put the table youre joining to be the left hand side of the ON clause you will encounter no problems

[cjard]

There's a restriction: subquery must return only one record; if not, will raise an error.

i.e. the subquery-to-main-query correlation:


SELECT TC.TIPO_CPE,
TC.DESCR_CPE,
(SELECT ST.DESCRIZIONE
FROM SOTTOTIPI_CPE ST
WHERE TC.TIPO_CPE = ST.TIPO_CPE
)
FROM TIPI_CPE TC
WHERE TC.TIPO_CPE = 37
Must be a 1:1 relationship. The subquery run on its own can return millions of rows, so long as they each uniquely map to at most one row of the correlated table.

I personally rarely use this approach but there are times when it is very useful