Get NULL values in a MySQL query

Question

I have two tables like these ones:

Table gene_names:

+------------------+------------------+
| gene_alias       | gene_name        |
+------------------+------------------+
| AC148152.3_FG007 | AC148152.3_FG007 |
| AC149829.2_FG002 | AC149829.2_FG002 |
| AC149829.2_FG004 | AC149829.2_FG004 |
| AC155352.2_FG009 | AC155352.2_FG009 |
| AC155352.2_FG012 | AC155352.2_FG012 |
+------------------+------------------+

And another table called gene_transcripts:

+-------------------+------------------+
| transcript_alias  | gene_alias       |
+-------------------+------------------+
| AC148152.3_FGT007 | AC148152.3_FG007 |
| AC149829.2_FGT002 | AC149829.2_FG002 |
| AC149829.2_FGT004 | AC149829.2_FG004 |
| AC155352.2_FGT009 | AC155352.2_FG009 |
| AC155352.2_FGT012 | AC155352.2_FG012 |
+-------------------+------------------+

What I want is to get the gene_alias from gene_transcripts and the gene_name from gene_names. For doing this, I do (example):

SELECT gene_transcripts.gene_alias,
       gene_names.gene_name
FROM gene_transcripts,
     gene_names
WHERE gene_transcripts.gene_alias="AT4G38300_oG.1"
  AND gene_transcripts.gene_alias=gene_names.gene_alias;

It gives no results because the gene_alias "AT4G38300_oG.1" does not have an entry in table gene_names. Up to here, it seems very logical and reasonable.

What I'd like to get (and I don't know how to achieve this, for this reason I ask here this question), is how to get the following output:

+-------------------+------------------+
| transcript_alias  | gene_alias       |
+-------------------+------------------+
| AT4G38300_oG.1    | NULL             |
+-------------------+------------------+

Is that possible?

You should edit your gene_transcripts example table to match your question. I've created a fiddle. — VMai
– VMai, Commented Jul 4, 2014 at 12:09

Raphaël Althaus · Accepted Answer · 2014-07-04 11:24:33Z

1

just use a left join

select 
   gt.gene_alias as transcript_alias, 
   gn.gene_name as gene_alias 
from gene_transcripts gt
left join gene_names gn on gt.gene_alias=gn.gene_alias
where gt.gene_alias="AT4G38300_oG.1"

answered Jul 4, 2014 at 11:24

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

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

Comments

Sadikhasan · Accepted Answer · 2014-07-04 11:25:13Z

0

SELECT gene_transcripts.gene_alias,
       gene_names.gene_name
FROM gene_transcripts,
     gene_names
WHERE (gene_transcripts.gene_alias="AT4G38300_oG.1"
  AND gene_transcripts.gene_alias=gene_names.gene_alias) 
  OR gene_transcripts.gene_alias IS NULL;

answered Jul 4, 2014 at 11:25

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

1 Comment

VMai Over a year ago

You can't create an implicit LEFT JOIN with a CROSS JOIN and the WHERE clause without special operators (deprecated in ms sql server and oracle long ago). Have a look at my fiddle.

Collectives™ on Stack Overflow

Get NULL values in a MySQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related