CODEWITHSUNDEEP
mysqlnull
user2979409
user2979409

Reputation: 791

Get NULL values in a MySQL query

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?

Upvotes: 0

Views: 43

Answers (2)

Sadikhasan
Sadikhasan

Reputation: 18600

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;

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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"

Upvotes: 1

Related Questions