Apex. How to output NULL values too?

Question

I had a question with a single sql query related to an interactive table in apex

Here, look, I prescribe a request in which I print all the existing artists in the database, but I only have entries where all the fields have values, and those in which at least one NULL are not displayed

select artist.name as "Artist", country.name as "Country" , city.name as "City of foundation", label.name as "Label of records"  
from artist, country, city, label
where artist.country = country_id
and artist.city = city_id
and city.country = country_id
and artist.label = label_id

How to fix it?

https://i.sstatic.net/ZRYzm.png

Sorry, you posted in Russian on an English conversation board. Stack Overflow has a Russian language section, consider deleting this post and asking your question there instead. ru.stackoverflow.com (Or ask someone to translate your question to English.) — user5683823
– user5683823, Commented Mar 10, 2019 at 15:33
"Apex. How to output NULL values too?" educated guess drop the old comma join usage which only can do cross join or inner joins and use proper ... LEFT JOIN ON ... syntax — Raymond Nijland
– Raymond Nijland, Commented Mar 10, 2019 at 15:37
Just as @Raymond, I'd say that it is OUTER JOIN you might need. Though, Raymond, the old comma join in Oracle supports outer joins as well - it is the (+) operator that does that. — Littlefoot
– Littlefoot, Commented Mar 10, 2019 at 15:49
"Though, Raymond, the old comma join in Oracle supports outer joins as well - it is the (+) operator that does that" iám aware of the (+) operator in Oracle database @Littlefoot which Oracle themself recommends not to use — Raymond Nijland
– Raymond Nijland, Commented Mar 10, 2019 at 15:51
No problem with that, @Raymond. I was only saying that your statement that the old comma join can only do cross or inner joins. Well, it can do outer joins as well. — Littlefoot
– Littlefoot, Commented Mar 10, 2019 at 15:57

Littlefoot · Accepted Answer · 2019-03-10 16:02:45Z

2

As you didn't provide test case (a screenshot isn't quite enough - at least, not to me), I'll try to show what might be going on using Scott's schema.

There are 4 departments: note department 40, and the fact that nobody works in it:

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from emp where deptno = 40;

no rows selected

If you want to display all 4 departments and employees who work in them, you'd join EMP and DEPT table. Outer join lets you display department 40 (which, as we saw, has no employees):

SQL> select d.deptno, d.dname, e.ename
  2  from dept d left join emp e on e.deptno = d.deptno      --> outer join is here
  3  order by d.deptno;

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     MILLER
        10 ACCOUNTING     KING
        20 RESEARCH       JONES
        20 RESEARCH       SMITH
        20 RESEARCH       SCOTT
        20 RESEARCH       FORD
        20 RESEARCH       ADAMS
        30 SALES          WARD
        30 SALES          TURNER
        30 SALES          ALLEN
        30 SALES          JAMES
        30 SALES          MARTIN
        30 SALES          BLAKE
        40 OPERATIONS                       --> this is what you're looking for

15 rows selected.

SQL>

You'd get the same result using the old Oracle's (+) outer join operator. You'd rather switch to modern joins and avoid that operator, though.

SQL> select d.deptno, d.dname, e.ename
  2  from dept d, emp e
  3  where d.deptno = e.deptno (+)        --> the old outer join operator
  4  order by d.deptno;

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     MILLER
        10 ACCOUNTING     KING
        20 RESEARCH       JONES
        20 RESEARCH       SMITH
        20 RESEARCH       SCOTT
        20 RESEARCH       FORD
        20 RESEARCH       ADAMS
        30 SALES          WARD
        30 SALES          TURNER
        30 SALES          ALLEN
        30 SALES          JAMES
        30 SALES          MARTIN
        30 SALES          BLAKE
        40 OPERATIONS

15 rows selected.

SQL>

answered Mar 10, 2019 at 16:02

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raymond Nijland Over a year ago

Also see how unlogical the (+) operator usage is you would expecting because the (+) is on the right side you could be thinking to get a RIGHT JOIN instead of the LEFT JOIN.when you see it for the first time ever...

Littlefoot Over a year ago

Right, @Raymond. There are numerous restrictions that apply to that operator; the worst disadvantage is - from my point of view - disability to outer join one table to more than only one another table (no wonder Oracle discourages its usage) (more info here: docs.oracle.com/cd/B14117_01/server.101/b10759/…).

Collectives™ on Stack Overflow

Apex. How to output NULL values too?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related