On Mon, 2004-06-28 at 09:32, Joseph Norris wrote:
> Group,
>
> First of all thanks to anyone who can respond to this - I am really stumped.
>
>
> I have been trying to figure this one out and maybe someone out there with a
> little deep understanding of joins in sql can give me a hand.
I think you're confusing the purpose of JOINs. The first statement you
gave DOES do a join even though the SQL doesn't explicitly have the word
'join' in it. Its implicit and its called an 'inner join'.
Your second query that has 'left join' in it is called an 'outer join'.
Outer joins can return different datasets than inner joins and neither
have anything to do with speed optimizations. In your last two examples
switching which tables are on which side of the join will have major
implications for what data you will get back. The fact that they all
return you the same data for the query you are running is coincidence.
If you have a book on SQL I'd suggest you check out chapter on joining.
Also, I googled this:
http://www.w3schools.com/sql/sql_join.asp
which seems to do a decent job of explaining things.
> I am working on a system that creates these sql statements on the fly and so
> the table names and fields are really perl variables.
>
> This the sql without the join:
>
>
> select
> cs_fld_cs_tbl_l.cs_type,field_name,name,type,type_sql,rl_table,cs_tbl from
> cs_fld, cs_fld_cs_tbl_l
> where cs_fld_cs_tbl_l.cs_fld_id = cs_fld.cs_rid
> and cs_fld_cs_tbl_l.cs_tbl_id = '23' AND cs_fld_cs_tbl_l.cs_type =
> 'basic'
>
>
>
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> | cs_type | field_name | name | type | type_sql |
> rl_table | cs_tbl |
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> | basic | status | Status | recordid | int |
> status | [23] |
> | basic | body | Main Body | textarea | text |
> | [23] |
> | basic | section | Section | recordid | int |
> demsect | [23] |
> | basic | title | Title | text | varchar(50) |
> | [23] |
> | basic | assignu | Assign to User: | recordid | int | u
> | [23] |
> | basic | subsect | Subsection | recordlist | list |
> subsect | [23] |
> | basic | assigngr | Assign to Group: | recordid | int |
> cs_com | [23] |
> | basic | sorder | Relative Order | numeric | int |
> | [23] |
> | basic | con_type | Content Type: | recordid | int |
> con_type | [23] |
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> 9 rows in set (0.01 sec)
>
>
>
>
>
> This is the join:
>
> select
> cs_fld_cs_tbl_l.cs_type,field_name,name,type,type_sql,rl_table,cs_tbl from
> cs_fld left join cs_fld_cs_tbl_l
> ON cs_fld_cs_tbl_l.cs_fld_id = cs_fld.cs_rid
> WHERE cs_fld_cs_tbl_l.cs_tbl_id = '23' AND cs_fld_cs_tbl_l.cs_type =
> 'basic'
>
>
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> | cs_type | field_name | name | type | type_sql |
> rl_table | cs_tbl |
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> | basic | status | Status | recordid | int |
> status | [23] |
> | basic | body | Main Body | textarea | text |
> | [23] |
> | basic | title | Title | text | varchar(50) |
> | [23] |
> | basic | subsect | Subsection | recordlist | list |
> subsect | [23] |
> | basic | assigngr | Assign to Group: | recordid | int |
> cs_com | [23] |
> | basic | section | Section | recordid | int |
> demsect | [23] |
> | basic | sorder | Relative Order | numeric | int |
> | [23] |
> | basic | assignu | Assign to User: | recordid | int | u
> | [23] |
> | basic | con_type | Content Type: | recordid | int |
> con_type | [23] |
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> 9 rows in set (4.44 sec)
>
>
>
> Notice the time difference? I thought that the join statement was supposed
> to be more efficient.
>
>
> I did some dinking with the joins and I go this:
>
> It appears that optimization of joins depends upon what table you are
> joining to what:
>
>
> This is the new sql:
>
> mysql> select
> -> cs_fld_cs_tbl_l.cs_type,field_name,name,type,type_sql,rl_table,cs_tbl
> from
> -> cs_fld_cs_tbl_l left join cs_fld
> -> ON cs_fld_cs_tbl_l.cs_fld_id = cs_fld.cs_rid
> -> WHERE cs_fld_cs_tbl_l.cs_tbl_id = '23' AND cs_fld_cs_tbl_l.cs_type
> = 'basic';
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> | cs_type | field_name | name | type | type_sql |
> rl_table | cs_tbl |
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> | basic | status | Status | recordid | int |
> status | [23] |
> | basic | body | Main Body | textarea | text |
> | [23] |
> | basic | section | Section | recordid | int |
> demsect | [23] |
> | basic | title | Title | text | varchar(50) |
> | [23] |
> | basic | assignu | Assign to User: | recordid | int | u
> | [23] |
> | basic | subsect | Subsection | recordlist | list |
> subsect | [23] |
> | basic | assigngr | Assign to Group: | recordid | int |
> cs_com | [23] |
> | basic | sorder | Relative Order | numeric | int |
> | [23] |
> | basic | con_type | Content Type: | recordid | int |
> con_type | [23] |
> +---------+------------+------------------+------------+-------------+------
> ----+--------+
> 9 rows in set (0.01 sec)
> All I did was transpose the cs_fld table with the cs_fld_cs_tbl_l and bam! I
> get the more efficient time.
>
> My question is: How do I determine which table should be on which side of
> the join statement?
>
> Thanks to all who respond - I really am stumped on this one.
--
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑÐÐÐÐ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
