Left Join Overrides Non-Null Value with Null value when record is empty on second table-CodePudding

I have two tables:

Table 1: qtrade qtrade columns qtrade values

Table 2: qsale qsale columns qsale values

These two table have common "tid" which is unique trade id. I need to get tid's with their qsale values if it is available. So, i tried to LEFT JOIN method like this:

'SELECT *
FROM `qtrade`
LEFT JOIN `qsale` ON qtrade.tid = qsale.tid'

The query retrieves joined data, but for tid=11 there is no qsale record, so it retrieves NULL valeus as expected, but also overrides tid with NULL value as not expected. It gets tid NULL.

I have serached that and found COALESCE trick. It might work, but i would write down all column names in qtrade and qsale, these are around 32 columns. Too long. If there any trick to overcome this issue. I think 'SELECT *, COALESCE(qsale.tid, qtrade.tid) tid' will not work. Meaning only coalesce tid, and get all column data. Is there any other way ?

CodePudding user response：

What you describe does work.

Demo:

mysql> create table qtrade (tid int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table qsale (tid int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into qtrade set tid=42;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT *
    -> FROM `qtrade`
    -> LEFT JOIN `qsale` ON qtrade.tid = qsale.tid;
 ------ ------ 
| tid  | tid  |
 ------ ------ 
|   42 | NULL |
 ------ ------ 
1 row in set (0.00 sec)

mysql> SELECT *, COALESCE(qsale.tid, qtrade.tid) AS tid 
 FROM `qtrade` LEFT JOIN `qsale` ON qtrade.tid = qsale.tid;
 ------ ------ ------ 
| tid  | tid  | tid  |
 ------ ------ ------ 
|   42 | NULL |   42 |
 ------ ------ ------ 
1 row in set (0.00 sec)

MySQL query result sets allow multiple columns to have the same name.

But the problem arises when you have a client that fetches the results into an associative array or hashmap, which only allows one entry per name.

In that case, the only alternative is to change the client code to fetch results into an ordinal array instead of an associative array, and then reference the columns of the result by position instead of by name.

I never use SELECT * in production code anyway. Just write out the columns. If typing 32 column names is the bottleneck in your programming productivity, then you're doing it wrong.