inner join can have null column

Question

I try to understand outer join and I got a question.

Assuming that all the columns are not null, after this query:

SELECT a.service_type_id, b.customer_member_id, a.service_name
FROM CUSTOMER_SERVICE_TYPE a,
     MEMBER_CUSTOMER_SERVICES b
WHERE a.service_type_id = b.service_type_id;

Is it possible to get null fields in one of the column?

your sample query is INNER JOIN which will check for match of service_type_id from both table. there will be no null values on the columns. if you use OUTER JOIN and there are data that didn't match your criteria (a.service_type_id = b.service_type_id) then there will be nulls on the columns. — Ferdinand Gaspar, Aug 27 '17 at 20:45
you can check this link about JOINS https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins — Ferdinand Gaspar, Aug 27 '17 at 20:48
Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Aug 28 '17 at 06:56
There is no OUTER JOIN in your query. Comma (`,`) is CROSS JOIN (with lower precedence than INNER/CROSS/OUTER JOIN), which is INNER JOIN ON 1=1. Please edit your question so that the title, text and code agree. — philipxy, Aug 28 '17 at 07:07

Gordon Linoff · Answer 1 · 2017-08-27T21:16:44.187

First, the title to your question says "outer join", but your query is an inner join.

Second, the query is malformed, using an archaic syntax. It should really be:

SELECT cs.service_type_id, mcs.customer_member_id, cs.service_name
FROM CUSTOMER_SERVICE_TYPE cs JOIN
     MEMBER_CUSTOMER_SERVICES mcs
     ON cs.service_type_id = mcs.service_type_id;

Third, the answer to your question is "no". None of the columns can be NULL if none of the source columns are NULL in your query. If it were really an OUTER JOIN, then columns could be NULL.

Notes:

Always use proper, explicit JOIN syntax. Never use commas in the FROM clause.
You table aliases should be abbreviations for the column names, rather than meaningless letters.

inner join can have null column

1 Answers1