replace inner join with select from, perhaps?

Question

I am trying to understand and modify an SQL query used by an input field to do a smart search.

The original code is here:

((`clients` INNER JOIN `addresstorecord` ON `clients`.`uuid` = 
   `addresstorecord`.`recordid` AND `addresstorecord`.
   `tabledefid`='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND
    addresstorecord.primary='1') INNER JOIN 
  `addresses` ON  `addresstorecord`.`addressid` = `addresses`.`uuid`)

I do not actually need an inner join, as all my information is already in one table.

In that case, could I theoretically just replace the inner join query with my table name? Or would I have to actually do a select from statement?

Answer 1

A query can use a join to restrict rows in the result set, not only to get additional columns to show. For example:

INSERT INTO tableA (col1) VALUES (10), (20), (30);
INSERT INTO tableB (col1) VALUES (20);

SELECT tableA.col1 FROM tableA;

Returns three rows: 10, 20, and 30.

SELECT tableA.col1 FROM tableA JOIN tableB ON tableA.col1 = tableB.col1;

Returns one row: 20.

So in your example, the join means the query returns only those clients who have a matching row in addresses.

Answer 2

If you remove the Joins, you will output all the rows in clients table. If that is what you want, you can remove the joins, but i don think so. The joins are actually filtering your output to give only the clients rows where clients.uuid = addresstorecord.recordid and so on. I think all the data you want to see in output is in the clients table but the data you need to filter is not so you need the joins.