6

I'm learning SQL/dbms and using Postgres. I want to return rows that both have a certain value in a certain column. For example in tables Carpets and Curtains, I want to get the ids of rows where the colour is 'light yellow'. I think I need JOIN for this, but am not sure what type.

Here's what I've got:

SELECT id
  FROM Carpets
  WHERE colour = 'light yellow'
        INNER JOIN Curtains ON Carpets.colour = Curtains.colour;

Both tables have the id attribute.

About learning JOIN, which one should I learn first? I'm shooting my self in the foot if I try learning all of them at once (as different resources included different 'variants').

IMPORTANT I was looking for an answer where the id would be returned only if both curtains and carpet were 'light yellow'.

bldoron
  • 1,050
  • 4
  • 20
  • 37
Celeritas
  • 14,489
  • 36
  • 113
  • 194

4 Answers4

13

Have read your question on Meta about this particular question, let me explain why all three answers are indeed correct - as is the way that you worked it out.

I have included examples of all three answers and the schema that they are working on:

Database changed
mysql> create table carpet(id int(3), material varchar(10), color varchar(15));
Query OK, 0 rows affected (0.02 sec)

mysql> create table curtain(id int(3), material varchar(10), color varchar(15));
Query OK, 0 rows affected (0.00 sec)

(bunch of insert statements)

mysql> select * from carpet;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
+------+-----------+--------------+
4 rows in set (0.00 sec)

mysql> select * from curtain;
+------+----------+--------------+
| id   | material | color        |
+------+----------+--------------+
|    1 | Velvet   | Purple       |
|    2 | cotton   | White        |
|    3 | cotton   | Light Yellow |
|    4 | cotton   | Light Blue   |
+------+----------+--------------+
4 rows in set (0.00 sec)

An intersect uses two select statements and brings back matching results. In this case, you are looking for all the rows that have a matching color of 'Light Yellow'.

I can't give you an example in MySQL as it doesn't support it (As you can see below, it's not needed to give the same results).

A union query of two select statements each with a where clause only allowing the color of 'Light Yellow' will return the same data. Although a union can be used to return data that doesn't match, the where clause in each select statement means that it will ondeed only return the rows that you want.

mysql> select id, material, color from carpet
    -> union 
    -> select id, material, color from curtain;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
|    1 | Velvet    | Purple       |
|    2 | cotton    | White        |
|    3 | cotton    | Light Yellow |
|    4 | cotton    | Light Blue   |
+------+-----------+--------------+
8 rows in set (0.00 sec)

Aww, that's bad right? Of course, we didn't specify the where clause:

mysql> select id, material, color from carpet where color='Light Yellow'
    -> union
    -> select id, material, color from curtain where color='Light Yellow';
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    3 | polyester | Light Yellow |
|    3 | cotton    | Light Yellow |
+------+-----------+--------------+
3 rows in set (0.00 sec)

A join between two tables on the color will allow you to return the rows from both the tables in a single row of data. You can specify the join on the two tables for the item color, and use a where clause to only return the rows that you are looking for.

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color;
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
2 rows in set (0.00 sec)

As you can see, this has returned only the rows with a matching color and allowed you to have columns from both tables in a single row of your resultset.

Now, I clearly didn't plan this very well as I have no other matching results apart from the 'Light Yellow' in both tables, so if I add a few more entries in we get this:

mysql> select * from curtain;
+------+----------+--------------+
| id   | material | color        |
+------+----------+--------------+
|    1 | Velvet   | Purple       |
|    2 | cotton   | White        |
|    3 | cotton   | Light Yellow |
|    4 | cotton   | Light Blue   |
|    5 | Wool     | White        |
|    6 | Fluff    | Beige        |
+------+----------+--------------+
6 rows in set (0.00 sec)

mysql> select * from carpet;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
|    5 | Fluff     | Light Blue   |
+------+-----------+--------------+
5 rows in set (0.00 sec)

Now we can run that again, and this time get:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color;
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
|    4 | cotton   | Light Blue   |    5 | Fluff     |
|    6 | Fluff    | Beige        |    2 | wool      |
+------+----------+--------------+------+-----------+
4 rows in set (0.00 sec)

Oh noes!

This is now where we use the join and the where clause together:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color 
    -> where a.color='Light Yellow';
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
2 rows in set (0.00 sec)

You see, in SQL there are often more ways to get the same result through different means than there are variations of the same data in your tables.

Edit: Okay, so if you only want rows where all the data matches, just include it in the join syntax:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a 
    -> join carpet b on a.color=b.color
    -> and a.id=b.id
    -> where a.color='Light Yellow';
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
1 row in set (0.00 sec)

As you can see, now we tell the join that both the id and color fields must match between the two tables - and the results speak for themselves. Now, in this case, I technically still didn't match ALL the columns as the material is different. If you wanted to match further, the query would not return any results as I have no matching records where the id, material AND color match, but the syntax would be as follows:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a 
    -> join carpet b on a.color=b.color
    -> and a.id=b.id
    -> and a.material=b.material
    -> where a.color='Light Yellow';
Empty set (0.00 sec)

On that note though, you in most cases, you don't want all the columns to match. Very often tables have an ID that is only used for that table and is an automatically incrementing value. You want to use it to identify a unique row in that table, but not to use it to match unrelated tables. If anything, I would have suggested that you match on material and color - but leave the ID out of it.

Community
  • 1
  • 1
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • What I would have wanted from your sample data is just id=3. No id=1 because even though in carpet colour="light yellow" in curtain colour="purple" for id=1. – Celeritas Sep 17 '12 at 08:38
  • Are you sure the query is logically equivalent to `select id from cutrain where colour='light yellow' intersect select id from carpet where colour='light yellow';`? – Celeritas Sep 17 '12 at 08:49
  • @Celeritas Yes, test it out for yourself. The query you just pasted there is no different to you using `union all` in place of intersect. Lastly, Understanding how joins work will let you do a *LOT* more than by using intersects alone. – Fluffeh Sep 17 '12 at 08:55
  • Yes that worked, must have typed in wrong the first time. Thanks a bunch! So the previous answers were in fact answering a different question (I ask this rhetorically as I had been getting too many rows)? – Celeritas Sep 17 '12 at 09:09
  • 1
    @Celeritas I think that they were all answering pretty close to what you wanted (and exactly what you asked for in the question but also assuming that you might expand their solutions further). What I wrote here is almost a customized tutorial on joins :P – Fluffeh Sep 17 '12 at 09:12
  • @Celeritas By the way, while I appreciate the token of gratitude in the unholy marathon of upvotes, the nasty algorithm will run tomorrow and take them all back away - read this: http://meta.stackexchange.com/a/126857/190322 – Fluffeh Sep 17 '12 at 09:28
  • 1
    @Celeritas Hey mate, wanted to suggest you have a look at this [question and answer](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) that I wrote today after posting this answer yesterday :) – Fluffeh Sep 18 '12 at 13:21
2

Since the results are for two disjoint tables, you actually want a union instead:

select id, 'curtains' as source from curtains where color = 'lightyellow'
  union 
select id, 'carpets' as source from carpets where color = 'lightyellow'

Regarding joins, learn them all together. They are just slight variations of each other.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Tanzeeb Khalili
  • 7,324
  • 2
  • 23
  • 27
  • You might want to include a literal string column in the query to show whether each row of output is describing a curtain id or a carpet id. – Fred Sobotka Sep 16 '12 at 03:21
  • @TanzeebKhalili what does the word "disjoint" as in disjoint tables mean? – Celeritas Sep 16 '12 at 10:08
  • It looks like `union` is what the poster wants. But in postgresql literals use single quotes as in `'curtains' as source`. Edited for you. – Clodoaldo Neto Sep 16 '12 at 10:14
2

If you want the result for matching in two table, then try this:

select id
  from curtains
      ,carpets
  where curtain.color = carpets.color;

This will return id where curtain.color = carpets.color

Glenn
  • 8,932
  • 2
  • 41
  • 54
Shriram Sapate
  • 155
  • 3
  • 6
  • 15
2

These both query will give you result which you want....

SELECT Carperts.id
  FROM Carpets INNER JOIN Curtains ON Carpets.colour = Curtains.colour
  and colour = 'light yellow';


SELECT Carperts.id
  FROM Carpets INNER JOIN Curtains ON Carpets.colour = Curtains.colour
  WHERE colour = 'light yellow';
Hunter
  • 820
  • 8
  • 19