0

Let's say there are 2 or more tables.

Table A: aID, name, birthday

Table B: bID, petType, petName

Table C: cID, stackOverFlowUsername

I want to get something like aID, name, birthday, number of cats a person has, stack overflow's username

We can

  • use joins to join all 3 tables select * from tableA... tableB... tableC...
  • use multiple select statements, select a.*, (select count(*) from tableB where petType = 'cat') as numberOfCats, (select...) as stackUsername from tableA a
  • or other ways that I didn't know

My question is when is the right situation to use select, joins or is there even better methods?

Update:

Here is another question. If I have 3 stackoverflow accounts, Tom has 1 and Peter has 2, using

A left join B left join C

will return a total of 6 rows

select a.*, select count(*) from tableB where..., select top 1 stackOverFlowUsername from tableC

returns 3 rows because there are 3 person

Can I use joins to achieve something similar if I only want one row of data for each person in tableA regardless how many stackoverflow accounts he/she has?

Thanks

BurningJade
  • 1
  • 3

1 Answers1

0

A selected sub-select (case 2) might be scanned for every result row, while joined tables/views/subselects are calculates only once: saving memory and joining time (with pre-built indices). Once you are used to talking SQL, you will find that the JOIN syntax is many times easier to read.

Levente Pánczél
  • 1,895
  • 2
  • 14
  • 16
  • Hi, am I right to say if I do something like this wastes more memory as compared to a join? select a.*, select col1 from tableC where..., select col2 from tableC where..., select col3 from tableC where... – BurningJade Mar 03 '14 at 02:53