How to do a join on 2 tables, but only return the data for one table?

I am not sure if this is possible. But is it possible to do a join on 2 tables, but return the data for only one of the tables. I want to join the two tables based on a condition, but I only want the data for one of the tables. Is this possible with SQL, if so how? After reading the docs, it seems that when you do a join you get the data for both tables. Thanks for any help!

4 answers

  • answered 2018-04-14 15:47 dani herrera

    You get data from both tables because join is based on "Cartesian Product" + "Selection". But after the join, you can do a "Projection" with desired columns.

    SQL has an easy syntax for this:

    Select t1.*               --taking data just from one table
    from one_table t1
    inner join other_table t2
    on t1.pk = t2.fk
    

    You can chose the table through the alias: t1.* or t2.*. The symbol * means "all fields".

    Also you can include where clause, order by or other join types like outer join or cross join.

  • answered 2018-04-14 15:47 O. Jones

    A typical SQL query has multiple clauses.

    The SELECT clause mentions the columns you want in your result set.

    The FROM clause, which includes JOIN operations, mentions the tables from which you want to retrieve those columns.

    The WHERE clause filters the result set.

    The ORDER BY clause specifies the order in which the rows in your result set are presented.

    There are a few other clauses like GROUP BY and LIMIT. You can read about those.

    To do what you ask, select the columns you want, then mention the tables you want. Something like this.

       SELECT t1.id, t1.name, t1.address
         FROM t1
         JOIN t2 ON t2.t1_id = t1.id
    

    This gives you data from t1 from rows that match t2.

    Pro tip: Avoid the use of SELECT *. Instead, mention the columns you want.

  • answered 2018-04-14 15:47 Gordon Linoff

    This would typically be done using exists (or in) if you prefer:

    select t1.*
    from table1 t1
    where exists (select 1 from table2 t2 on t2.x = t1.y);
    

    Although you can use join, it runs the risk of multiplying the number of rows in the result set -- if there are duplicate matches in table2. There is no danger of such duplicates using exists (or in). I also find the logic to be more natural.

  • answered 2018-04-14 15:47 hang gao

    If you join on 2 tables.

    You can use SELECT to select the data you want

    If you want to get a table of data, you can do this,just select one table date

    SELECT b.title
    FROM blog b
    JOIN type t ON b.type_id=t.id;
    

    If you want to get the data from two tables, you can do this,select two table date.

    SELECT b.title,t.type_name
    FROM blog b
    JOIN type t ON b.type_id=t.id;