Mysql – Which join to use

MySQLPHP

So I have a database I'm trying to convert to a new one, it has around 40k rows in let's call it table A, now the actual data is in table B which has over 1.6 mil rows.

When I'm writing my PHP script I'm calling the DB every time to connect table B instead of using a join and due to the mass amount of queries its stopping on row 3205 or something, but when I don't connect table B it goes all the way to end.

What I want to do it have a neat way of connecting table A to table B in one query but I suck with joins.

Here's what the problem looks like:

Table A

ID
Something
Something 2
etc...

Table B

ID
TableA ID 
Something

Of the top of my head I'm thinking I'd join table A onto table B and table B has the majority of records but again as I said I'm no good at joins.

SELECT * 
FROM  `Table B` 
RIGHT JOIN  `Table A` 
ON  `Table B`.`Table A ID`=`Table A`.`Table A ID`

Runs a little slow. Any ideas?

Best Answer

Cheat sheet:

SELECT  *
    FROM  B
    JOIN  A  ON B.x = A.x

finds rows that are in both tables. INNER JOIN is a synonym.

SELECT  *
    FROM  B
    LEFT JOIN  A  ON B.x = A.x
    WHERE A.x IS NULL;

finds rows that are in B but not A.

RIGHT is LEFT with the table names swapped everywhere. (Nobody uses RIGHT.)

CROSS JOIN a regular JOIN, but without an ON. You get all combinations of rows in the two tables. (Unless the WHERE tosses some.)

Be sure to always use qualify all columns with the table it comes from. If the table names are long; use aliases (FROM long_table_name AS ltn)

Index the column (or combination of columns) in the ON. Often it is the PRIMARY KEY in one of the tables. A PRIMARY KEY is an index; don't add another index.

Related Question