Not possible as far as I know, but this can be done with LINQ, search on net with your favourite search engine with keyword LINQ and SHAREPOINT JOIN LISt and you will get some ready made code.

i guess there is way of doing it.....i just read about an article on spdataquery.....just check it out

you can do that by querying sharepoint content database.
just try with that

we can use SPSiteDataQuery Class to get data from multiple sharepoint lists, which may be located in multiple Web sites in the same Web site collection.


Check out my blog for code and more information  on this.

http://sharingmypoint.blogspot.com/

join is not possible in the list as they are not RDBMS.
but through good coding logic, u can achieve it , this will make data access slow , but will work fine,
and if u r trying to generate reports than sqlreporting service can help u in joining two lists

If you need to perform joins between SharePoint lists, try WICE JoinPro.
WICE JoinPro is a powerful tool that allows you to realize all kind of join  (inner, full, left, right) between your lists  and apply sorting, filtering, grouping and paging options without using SharePoint Designer.
Through its user friendly editor, you will be able to configure your data source defining relations between multiple lists coming from different sites and servers using endless parameters (from 2 to n).
JoinPro has a powerful WebPart that can easily display your data in any SharePoint page.
Get the trial version and check our website for more details!
http://sharepoint.wice-group.com/

You can use CAML to join  data from 2 different lists  and store it in a dataset or datatable. And then programatically insert this data from dt into a new custom sp list.