Select from two tables by unique Id [closed]

I have two tables calendar and team, to simplify : in calendar table i have Id, team_id1, team_id2 and date, and in team table i have Id, name and image. I have to select all columns of calendars and also team names and images associated, i used this query, it works but i think i execute more than 3 queries in one. Can you suggest me more effective please :

SELECT *, 
calendar.team_id1,calendar.team_id2,
(select team.Name from team where team.Id = calendar.team_id1 ) AS 'TeamName1',
(select team.Name from team where team.Id = calendar.team_id2 ) AS 'TeamName2',
(select team.Image from team where team.Id = calendar.team_id1 ) AS 'TeamImage1',
(select team.Image from team where team.Id = calendar.team_id2 ) AS 'TeamImage2'
FROM calendrier ORDER BY calendar.Date DESC

Thank you.

Answer

I really can´t see a reason to make use of subqueries to solve the related problem.

So that, the solution is a basic query with 2 JOINs on table “team”, as follow sql:

SELECT calendar.*, 
team1.Name  AS "TeamName1", team1.Image AS "TeamImage1",
team2.Name  AS "TeamName2", team2.Image AS "TeamImage2"
FROM calendar 
LEFT JOIN team team1 on (team1.Id = calendar.team_id1)
LEFT JOIN team team2 on (team2.Id = calendar.team_id2)
ORDER  BY calendar.Date DESC

See that above I have replaced 4 subSelects for 2 joins clauses, it is more direct and even more efficient.

SubSelects are useful in many situations for select-fields (projection) and where-statament (selection), but not for this simple relation beetwen 2 tables that can be joined by its PK e FK fields.

note: in order to test the syntax of my sugested sql, I use follow DML in h2DB:

create table calendar(
Id integer, team_id1 integer, team_id2 integer, Date Date
);
create table team (
Id int, Name character(200), Image blob
);

You can see discussion about JOIN VS SubQuery here: Join vs. sub-query